Code for macro variables

*GENERATE LIST OF FILES IN RAWDATA DIRECTORY;
FILENAME FILES PIPE "DIR ""&PATH.\RAWDATA"" /B";
DATA A_FILES B_FILES OTHER_FILES;
LENGTH FILE1 1$ 12 SOURCE $ 2 B_UNIT $ 5;
INFILE FILES TRUNCOVER;
INPUT FILE $12.; IF COMPRESS(FILE1) NE "" AND LENGTH(FILE1) GE 12; SOURCE = SUBSTR(COMPRESS(UPCASE(FILE1)),7,2);B_UNIT = SUBSTR(COMPRESS(UPCASE(FILE1)),1,5); IF COMPRESS(UPCASE(SOURCE)) EQ "AA" THEN OUTPUT A_FILES; ELSE IF COMPRESS(UPCASE(SOURCE)) EQ "BB" THEN OUTPUT B_FILES; ELSE OUTPUT OTHER_FILES;RUN;


*GENERATE A LIST OF GLOBAL REFERENCES TO THE FILE NAMES CONTIANED IN THE DATASET;
DATA _NULL_;
SET A_FILES;
CALL SYMPUT("FILE_NAME"COMPRESS(_N_),PUT(FILE1,$12.));
RUN;
%PUT _ALL_;
*FIND OUT HOW MANY FILES ARE IN THE DATASET;
PROC SUMMARY DATA = A_FILES NWAY;
OUTPUT OUT = RECORD_CNT_A (DROP=_TYPE_);
RUN;
*CREATE THE MAXIMUM COUNTER VALUE FOR THE MACRO DO LOOP;DATA _NULL_;S
ET RECORD_CNT_A;
CALL SYMPUT("RC",_FREQ_);
RUN;

*MAINTAIN APPEND PROCEDURE BASE DATASETS TO PREVENT DUPLICATION;
PROC DATASETS LIBRARY = WORK NOLIST; DELETE FILE_DATASET_ALL;
RUN;
QUIT;

MACRO IMPORT_XLS(FILE_NAME);
*IMPORT EXCEL REPORT IMAGE FILES;
PROC IMPORT OUT = READDATA
DATAFILE = "&PATH.\RAWDATA\&FILE_NAME." DBMS = EXCEL REPLACE; GETNAMES = NO; MIXED = YES;RUN;
%MEND;

*FEED ALL THE FILES IN THE DIRECTORY INTO THE MACRO;
%MACRO RUN_LOOP(X);
%DO I = 1 %TO &RC.;
%IMPORT_XLS(&&FILE_NAME&I.);
%END;
%MEND RUN_LOOP;
%RUN_LOOP(1);


*APPENDS THE MULTIPLE FILES FOR PROCESSING;
PROC APPEND BASE = FILE_DATASET_ALL DATA = FILE_DATASET FORCE; RUN;

***MACRO VARIABLE TO COUNT***;
PROC SQL NOPRINT; SELECT COUNT(*) INTO :NBR FROM &MAIN_DSIN.;QUIT;

VBA SOLDIER

Sub Ins_Row()Sheets("sheetname").Select
R = Cells(1, 15).Valuec = Cells(1, 16).Value - 4s = Cells(1, 14).ValueSheets(s).Visible = True

Application.Goto reference:=R
ZAP = ActiveCell.Row
Rows(ZAP & ":" & ZAP).SelectSelection.Copy
ZAP1 = ZAP + cRows(ZAP & ":" & ZAP1).SelectSelection.Insert Shift:=xlDown
End Sub

Sub GOBACK4()
Cells(1, 1).Select Application.ActiveWorkbook.Worksheets("3.Engineering").Activate Range("N7").Select 'Range("Missing_header").Select Sheets("sem1_marksheet").Visible = False
End Sub

Sub VIEWTEN() Sheets("10_marksheet").Visible = True Application.ActiveWorkbook.Worksheets("10_marksheet").Activate Range("A6").SelectEnd Sub

CODE FOR DAILY USE

DM LOG 'CLEAR' LOG;OPTIONS MPRINT MLOGIC SYMBOLGEN NOCENTER NOLABEL;
%LET PATHNAME = G:\xxxx\yyyy\;LIBNAME SASDATA "&PATHNAME.\SAS DATA";
FILENAME ABC "&PATHNAME.\mmm.txt";FILENAME XYZ "&PATHNAME.\kkk.xls";

*CALLS MACROS PROGRAM;%INCLUDE "&PATH.\PROGRAMS\MACROS.sas";%MEND;
*SET EXTERNAL LOG;PROC PRINTTO PRINT = "&PATH.\PROGRAMS\&PROGRAM..DOC" LOG = "&PATH.\PROGRAMS\ &PROGRAM..DOC" NEW;RUN;
*CALCULATE SUMMARY ;PROC SQL NOPRINT; SELECT COUNT(*) INTO:NBR_TOT_REC FROM ALL_DATASET;QUIT;
PROC SQL NOPRINT; CREATE TABLE DATASET_SUMMARY AS SELECT SUM(VAR1) AS SUM_VAR1, SUM(VAR2) AS SUM_VAR2, SUM(VAR3) AS SUM_VAR3, FROM ALL_DATASET;QUIT;
*CALCULATE SUMMARY OF INFORMATION;PROC SQL NOPRINT; SELECT COUNT(*) INTO:NBR_RECORD FROM DATASET WHERE > x;QUIT;
DATA _NULL_; PERCENT_RECORD = &NBR_RECORD./&NBR_TOT_REC.; CALL SYMPUT('PERCENT_RECORD',PERCENT_RECORD);RUN;

*SET LOG BACK TO DEFAULT;PROC PRINTTO PRINT = PRINT LOG = LOG NEW;RUN;

*MACRO INSERTS ROWS INTO SPECIFIED EXCEL SPREADSHEET *;
%MACRO INSERTROW(FILENAME,DOUT,REF,SHEET);
%GLOBAL COUNT;
%*** TO COUNT THE NUMBER OF OBSERVATIONS **; DATA _NULL_; IF 0 THEN SET &DOUT. NOBS=COUNT; CALL SYMPUT('COUNT',COUNT); STOP; RUN;%*** TO CREATE A DATASET WITH REFERENCE NAME AND SHEET NAME WHERE ROWS NEED TO BE INSERTED **; %END;
DATA REFERENCE; SEARCH = &REF.; SHEET_NAME = &SHEET.; COUNT = &COUNT.; RUN;
FILENAME SASTOXL DDE "EXCEL[&FILENAME.]SHEETNAME_??! R1C14:R1C16" NOTAB; DATA _NULL_; FILE SASTOXL DLM='09'X NOTAB; SET REFERENCE; PUT SHEET_NAME SEARCH COUNT; RUN;
** TO CALL THE INSERT ROW VBA MACRO **; %IF &COUNT. > 3 %THEN %DO; FILENAME CMDS DDE "EXCELSYSTEM! " NOTAB; DATA _NULL_; FILE CMDS; PUT '[RUN("INS_ROW")]'; RUN; %END;
%MEND INSERTROW;
*MACRO EXPORTS DATA TO TEMPLATE(S) *;%MACRO EXPORTDDE(PRELIM,UN_DSIN,MAIN,PARENT);*OPEN EXCEL;FILENAME SAS2XL DDE 'EXCELSYSTEM';DATA _NULL_; LENGTH FID RC START STOP TIME 8; FID = FOPEN('SAS2XL','S'); IF (FID LE 0) THEN DO; RC = SYSTEM('START EXCEL'); START = DATETIME(); STOP = START + 10; DO WHILE (FID LE 0); FID = FOPEN('SAS2XL','S'); TIME = DATETIME(); IF (TIME GE STOP) THEN FID = 1; END; END; RC=FCLOSE(FID);RUN;
*OPEN SPECIFIED EXCEL WORKBOOK;%LET XL_BK = "[OPEN(""&PATH.\OUTPUT\&PRELIM."")]";DATA _NULL_; FILE SAS2XL; PUT '[ERROR(FALSE)]'; PUT &XL_BK;RUN;
*ADD TITLES TO WORKSHEETS;FILENAME CONT DDE "EXCEL[&PRELIM.]sheetname_??!R1C1:R1C1" NOTAB ;DATA _NULL_; FILE CONT DLM='09'X NOTAB ; CT_TITLE="ABC : "UPCASE("XXXX")" - ""&PARENT."; PUT CT_TITLE;RUN;FILENAME CONT CLEAR;
FILENAME CONT DDE "EXCEL[&PRELIM.]SHEET_NAME???!R1C1:R1C1" NOTAB ;DATA _NULL_; FILE CONT DLM='09'X NOTAB ; CT_TITLE="ABC : "UPCASE("XXXX")" - ""&PARENT."; PUT CT_TITLE;RUN;FILENAME CONT CLEAR;

***UPLOADING THE SUMMARY ***;FILENAME RECDDE DDE "EXCEL[&PRELIM.]SHEETNAME???!TABLE_REC_1A_SUMM" NOTAB LRECL=1000;DATA _NULL_; SET &UN_DSIN.; FILE RECDDE DLM='09'X NOTAB; NBR_RECORD = INPUT(COMPRESS("&NBR_RECORD."),32.); PERCENT_RECORD = INPUT(COMPRESS("&PERCENT_RECORD."),32.2); FORMAT NBR_RECOD COMMA32. PERCENT_RECORD PERCENT32.2; FORMAT SUM_VAR1 COMMA32. SUM_VAR2 COMMA32.2; PUT NBR_RECORD PERCENT_RECORD SUM_VAR1 SUM_VAR2 RUN;FILENAME RECDDE CLEAR;
***UPLOADING THE REULTS ***;PROC SQL NOPRINT; SELECT COUNT(*) INTO :NBR FROM &MAIN;QUIT;
%IF &NBR. <= 65000 AND &NBR. > 3 %THEN %DO; *** INSERT ROWS FOR ACCOUNTS ***; %INSERTROW(&PRELIM., &MAIN., "REF1","SHEET NAME????");
FILENAME RECDDE2 DDE "EXCEL[&PRELIM.]SHEETNAME???!CEL_REFRENCE" NOTAB LRECL=1000; DATA _NULL_; SET &MAIN.; FILE RECDDE2 DLM='09'X NOTAB; PUT XXX VAR1 VAR2; FORMAT VAR1 VAR 2 32.2; RUN; FILENAME RECDDE2 CLEAR;%END;%ELSE %IF &NBR. > 65000 %THEN %DO; DATA REC_1B_ACCT2; SET &MAIN_DSIN.(OBS=65000); RUN;
%INSERTROW(&PRELIM., &MAIN., "REF1","SHEET NAME????");
FILENAME RECDDE2 DDE "EXCEL[&PRELIM.]SHEETNAME???!CEL_REFRENCE" NOTAB LRECL=1000; DATA _NULL_; SET &MAIN.; FILE RECDDE2 DLM='09'X NOTAB; PUT XXX VAR1 VAR2; FORMAT VAR1 VAR 2 32.2; RUN; FILENAME RECDDE2 CLEAR;
FILENAME RECDDE4 DDE "EXCEL[&PRELIM.]SHEETNAME???!UNRECMSG1" NOTAB LRECL=1000; DATA _NULL_; LENGTH T $300; FILE RECDDE4; T="ONLY THE FIRST 65,000 LINES ARE DISPLAYED HERE."; PUT T; RUN; FILENAME RECDDE4 CLEAR;%END;%ELSE %IF &NBR. >0 AND &NBR. <=3 %THEN %DO;%INSERTROW(&PRELIM., &MAIN., "REF1","SHEET NAME????");
FILENAME RECDDE2 DDE "EXCEL[&PRELIM.]SHEETNAME???!CEL_REFRENCE" NOTAB LRECL=1000; DATA _NULL_; SET &MAIN.; FILE RECDDE2 DLM='09'X NOTAB; PUT XXX VAR1 VAR2; FORMAT VAR1 VAR 2 32.2; RUN; FILENAME RECDDE2 CLEAR;%END;%MEND EXPORTDDE;