HI Below is my code libname sm "/sas/test/saswork"; %let workgroup=/sas/test/sasconfig/Lev2/AppData/SASRiskWorkGroup/groups/Retail; %let WHLworkgroup=/sas/test/sasconfig/Lev2/AppData/SASRiskWorkGroup/groups/Wholesale; %let RunMonth=Jun; %let RunDate=26; /*data _null_;*/ /*call symput('RunMonth',substr(strip(put(today(),monname.)),1,3));*/ /*call symput('RunDate',strip(put(today(),day.)));*/ /*run;*/ /*%put &RunMonth;*/ /*%put &RunDate;*/ %let runInstances=&workgroup./SASModelImplementationPlatform/output/run_instances ; %let WHLrunInstances=&WHLworkgroup./SASModelImplementationPlatform/output/run_instances ; filename runInst pipe "ls -al &runInstances | sed -r s'/\s+/|/g'" ; filename WHLInst pipe "ls -al &WHLrunInstances | sed -r s'/\s+/|/g'" ; %put &runInstances; %put &WHLrunInstances; /* X "ls -al &runInstances > /sas/dev/saswork/afolderlist.txt" ; filename runInst pipe "/sas/dev/saswork/afolderlist.txt" ; */ DATA WORK.folderlist; LENGTH F1 $ 10 F2 8 UserName $ 16 Domain $ 6 F5 $ 12 F6 8 RunMonth $ 12 RunDate $ 2 RunTime $ 40 RunName $ 40 ; FORMAT F1 $CHAR10. F2 BEST5. UserName $CHAR16. Domain $CHAR6. F5 $CHAR12. F6 BEST7. RunMonth $CHAR12. RunDate $CHAR1. RunTime $CHAR40. RunName $CHAR40. ; INFORMAT F1 $CHAR10. F2 BEST5. UserName $CHAR16. Domain $CHAR6. F5 $CHAR12. F6 BEST7. RunMonth $CHAR12. RunDate $CHAR2. RunTime $CHAR40. RunName $CHAR40. ; INFILE runInst LRECL=121 DLM='|' truncover DSD ; input @ ; INPUT F1 : $CHAR10. F2 : ?? BEST5. UserName : $CHAR16. Domain : $CHAR6. F5 : $CHAR12. F6 : ?? BEST7. RunMonth : $CHAR12. RunDate : $CHAR2. RunTime : $CHAR40. RunName : $CHAR40. ; RUN; DATA WORK.WHLfolderlist; LENGTH F1 $ 10 F2 8 UserName $ 16 Domain $ 6 F5 $ 12 F6 8 RunMonth $ 12 RunDate $ 2 RunTime $ 40 RunName $ 40 ; FORMAT F1 $CHAR10. F2 BEST5. UserName $CHAR16. Domain $CHAR6. F5 $CHAR12. F6 BEST7. RunMonth $CHAR12. RunDate $CHAR2. RunTime $CHAR40. RunName $CHAR40. ; INFORMAT F1 $CHAR10. F2 BEST5. UserName $CHAR16. Domain $CHAR6. F5 $CHAR12. F6 BEST7. RunMonth $CHAR12. RunDate $CHAR2. RunTime $CHAR40. RunName $CHAR40. ; INFILE WHLInst LRECL=121 DLM='|' truncover DSD ; input @ ; INPUT F1 : $CHAR10. F2 : ?? BEST5. UserName : $CHAR16. Domain : $CHAR6. F5 : $CHAR12. F6 : ?? BEST7. RunMonth : $CHAR12. RunDate : $CHAR2. RunTime : $CHAR40. RunName : $CHAR40. ; RUN; Data OnlyDomainFolderlist(drop=Domain F1 F2 F5 F6 runmonth rundate); *format RunTimeStamp datetime20.; set work.folderlist; RunTime = put(INTNX ('minute',input(RunTime,time.),-240),hhmm.); where Domain = 'domain' and RunMonth="&RunMonth" and rundate="&RunDate" and UserName not like '7%'; RunTimeStamp= catx(trim(RunMonth),trim(Rundate),trim(RunTime)); WorkGroup='Retail'; run; data _NULL_; set OnlyDomainFolderlist; call symputx(catx("_","MIPRUN",_N_),RunName); call symputx("NRUN", _N_); run; %macro MIP_SUMMARY; %do i = 1 %to &NRUN.; %put &runInstances./&&MIPRUN_&i.; %put &runInstances./&&MIPRUN_&i./logs; libname mipout "&runInstances./&&MIPRUN_&i."; libname miplog "&runInstances./&&MIPRUN_&i./logs"; data execution_log; format RUNNAME $32.; set mipout._execution_config_summary; where NAME in ("RUN_INST_NUM_SIMULATION" "RUN_INST_HORIZON" "RUN_INST_POST_EXEC_PGRM"); RUNNAME = "&&MIPRUN_&i."; keep RUNNAME NAME NUMERIC_VALUE TEXT_VALUE; run; data runstatus; format RUNNAME ACTION $32.; set miplog.runstatus; where (TASK_NM = "RUN_EXECUTION" and ACTION_NM in ("start" "completed")) or (TASK_NM = "POST_EXECUTION" and ACTION_NM = "completed"); RUNNAME = "&&MIPRUN_&i."; ACTION = CATX("_", TASK_NM, ACTION_NM); keep RUNNAME ACTION STEP_DTTM; run; %if &i = 1 %then %do; data execution_all; set execution_log; run; data runstatus_all; set runstatus; run; %end; %else %do; data execution_all; set execution_all execution_log; run; data runstatus_all; set runstatus_all runstatus; run; %end; %end; proc sql; create table execution_all as select RUNNAME, NAME, case when not missing(NUMERIC_VALUE) then strip(put(NUMERIC_VALUE, 32.)) else TEXT_VALUE end as VALUE from execution_all ; quit; proc sort data=execution_all; by RUNNAME; run; proc sort data=runstatus_all; by RUNNAME; run; proc transpose data=execution_all out=execution_out (drop=_NAME_); var VALUE; id NAME; by RUNNAME; run; proc transpose data=runstatus_all out=runstatus_out (drop=_NAME_); var STEP_DTTM; id ACTION; by RUNNAME; run; data totalruntime; set runstatus_out; keep RUNNAME EXECUTION_TIME TOTALTIME; EXECUTION_TIME = ROUND((RUN_EXECUTION_completed - RUN_EXECUTION_start)/60, 0.01); TOTALTIME = ROUND((POST_EXECUTION_completed - RUN_EXECUTION_start)/60, 0.01); run; %mend; %MIP_SUMMARY; proc sql; create table retail_summary as select A.*, B.RUN_INST_NUM_SIMULATION, B.RUN_INST_HORIZON, B.RUN_INST_POST_EXEC_PGRM, C.EXECUTION_TIME, C.TOTALTIME from OnlyDomainFolderlist as A left join execution_out as B on A.RUNNAME = B.RUNNAME left join totalruntime as C on A.RUNNAME = C.RUNNAME ; quit; Data WHLOnlyDomainFolderlist(drop = Domain F1 F2 F5 F6); *format RunTimeStamp datetime20.; set work.WHLfolderlist; where Domain = 'domain' and RunMonth="&RunMonth" and rundate="&RunDate" and UserName not like '7%'; RunTimeStamp= catx(trim(RunMonth),trim(Rundate),trim(RunTime)); WorkGroup='Wholesale'; run; data _NULL_; set WHLOnlyDomainFolderlist; call symputx(catx("_","MIPRUN",_N_),RunName); call symputx("NRUN", _N_); run; %macro WSL_MIP_SUMMARY; %do i = 1 %to &NRUN.; %put &WHLrunInstances./&&MIPRUN_&i.; %put &WHLrunInstances./&&MIPRUN_&i./logs; libname mipout "&WHLrunInstances./&&MIPRUN_&i."; libname miplog "&WHLrunInstances./&&MIPRUN_&i./logs"; data execution_log; format RUNNAME $32.; set mipout._execution_config_summary; where NAME in ("RUN_INST_NUM_SIMULATION" "RUN_INST_HORIZON" "RUN_INST_POST_EXEC_PGRM"); RUNNAME = "&&MIPRUN_&i."; keep RUNNAME NAME NUMERIC_VALUE TEXT_VALUE; run; data runstatus; format RUNNAME ACTION $32.; set miplog.runstatus; where (TASK_NM = "RUN_EXECUTION" and ACTION_NM in ("start" "completed")) or (TASK_NM = "POST_EXECUTION" and ACTION_NM = "completed"); RUNNAME = "&&MIPRUN_&i."; ACTION = CATX("_", TASK_NM, ACTION_NM); keep RUNNAME ACTION STEP_DTTM; run; %if &i = 1 %then %do; data execution_all; set execution_log; run; data runstatus_all; set runstatus; run; %end; %else %do; data execution_all; set execution_all execution_log; run; data runstatus_all; set runstatus_all runstatus; run; %end; %end; proc sql; create table execution_all as select RUNNAME, NAME, case when not missing(NUMERIC_VALUE) then strip(put(NUMERIC_VALUE, 32.)) else TEXT_VALUE end as VALUE from execution_all ; quit; proc sort data=execution_all; by RUNNAME; run; proc sort data=runstatus_all; by RUNNAME; run; proc transpose data=execution_all out=execution_out (drop=_NAME_); var VALUE; id NAME; by RUNNAME; run; proc transpose data=runstatus_all out=runstatus_out (drop=_NAME_); var STEP_DTTM; id ACTION; by RUNNAME; run; data totalruntime; set runstatus_out; keep RUNNAME EXECUTION_TIME TOTALTIME; EXECUTION_TIME = ROUND((RUN_EXECUTION_completed - RUN_EXECUTION_start)/60, 0.01); TOTALTIME = ROUND((POST_EXECUTION_completed - RUN_EXECUTION_start)/60, 0.01); run; %mend; %WSL_MIP_SUMMARY; proc sql; create table wholesale_summary as select A.*, B.RUN_INST_NUM_SIMULATION, B.RUN_INST_HORIZON, B.RUN_INST_POST_EXEC_PGRM, C.EXECUTION_TIME, C.TOTALTIME from WHLOnlyDomainFolderlist as A left join execution_out as B on A.RUNNAME = B.RUNNAME left join totalruntime as C on A.RUNNAME = C.RUNNAME ; quit; data mergedWHL_RTL_ALL (rename=(RUNTIME_NUM=RUNTIME)); format Env $5. workgroup $32. RUNTIME_NUM MMSS.; set retail_summary wholesale_summary; RUNTIME_NUM = SUBSTR(RUNTIME,1,2) * 60 + SUBSTR(RUNTIME, 4); Env="Test"; drop RUNTIME RunDate RunMonth; run; proc sort data=mergedWHL_RTL_ALL; by RUNTIME; run; /*data mergedWHL_RTL;*/ /*set work.WHLONLYDOMAINFOLDERLIST work.ONLYDOMAINFOLDERLIST;*/ /*environment="Dev";*/ /*run;*/ proc report data=work.mergedWHL_RTL_ALL; run; my report TestRetail2:18sweta.mukulkumarSM25CECL_CUBE_20190101_S9926Jun2:180 0.1.Env workgroup RUNTIME UserName RunName RunTimeStamp RUN_INST_NUM_SIMULATION RUN_INST_HORIZON RUN_INST_POST_EXEC_PGRM EXECUTION_TIME TOTALTIME Test Retail 2:19 sweta.mukulkumar SM25CECL_ECL_20190101_S99 26Jun2:19 10 1 Phase2_Business_Banking 0.13 0.19 Test Retail 11:51 paul.n.amaral PA_AUTO_CUBE_201903v2 26Jun11:51 0 0.52 . Test Retail 12:31 paul.n.amaral PA_ECL_AUTO_201903v2 26Jun12:31 100 96 Phase2_AUTO_CCAR_Balance_Adj . . Test Retail 13:25 anusha.jookuri ZHLNS_CCAR_CUBE_20190401_S99 26Jun13:25 0 0.08 . Test Retail 13:25 anusha.jookuri ZHLNS_CCAR_ECL_20190401_S99 26Jun13:25 1 32 Phase2_HELOAN_RECOVERY 0.11 . my concern is i want to remove observation if i dont have execution time
... View more
