libname fds "path1"; proc sql; create table count_fds as select MEMNAME AS DS_NAME,sum(nobs) as DS_COUNT, substr(MEMNAME,5,(anydigit(MEMNAME,1)-5)) as comm_Var1 from dictionary.tables where libname eq 'FDS' and MEMNAME CONTAINS "20170801" group by memname; quit; proc sql; select ds_name into : ds_nm separated by ' ' from count_fds; run; options symbolgen mprint; %macro tot_soa; %let i=1; %do %while (%scan(&ds_nm,&i,' ') ne ); proc sql; create table work.cnt_&i as select cat("%scan(&ds_nm,&i,' ')") as soa_ds , sum((case when cat("%scan(&ds_nm,&i,' ')") like '%_type1_%' then var1 when cat("%scan(&ds_nm,&i,' ')") like '%_type2_%' then var9 when cat("%scan(&ds_nm,&i,' ')") like '%_type3_%' then var14 when cat("%scan(&ds_nm,&i,' ')") like '%_type4_%' then var18 ELSE var21 end)) as soa_sum from fds.%scan(&ds_nm,&i,' ') t1; quit; %put %scan(&ds_nm,&i,' '); %let i=%eval(&i+1); %end; %mend tot_soa; %tot_soa;
... View more
