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;