There are many data sets in a library, as below.
CUST_201501
CUST_201502
CUST_201503
CUST_20150306
CUST_20150313
CUST_20150320
CUST_201501 is the month-end data for January
CUST_201503 is the month-end data for March
CUST_20150306 is the week-end data for 06th March 2015
CUST_20150313 is the week-end data for 13th March 2015
I want to achieve this:
data CUST_weekly;
set
CUST_20150306
CUST_20150313
CUST_20150320
run;
How do I go about automating the above? The below is the best I can come up with. Where do I put the SET statement?
data cust_weekly;
misdate='06MAR2015'd;
format misdate yymmddn8.;
j=1;
do while (j lt 5);
j+ +1;
put misdate=;
misdate=intnx('days',misdate,7);
end;
run;
Data-step and proc sql in one step:
proc sql noprint;
select catx('.', libname, memname)
into :memnames separated by ' '
from sashelp.vtable
where libname = 'libname' and memname contains 'CUST_201503'
;
quit;
If I interpret you right, you want to automate the set statement so that all weekly datasets for a given month are used for input.
proc sql;
create table int as
select libname, memname from dictionary.tables where libname='libname' and memname contains 'CUST_201503';
quit;
data _null_;
set int end=the_end;
length macrovar $ 200;
retain macrovar "";
macrovar=catx(" ",macrovar,trim(libname)!!'.'!!trim(memname));
if the_end then call symput('memnames',macrovar);
run;
data cust_weekly;
set &memnames;
run;
Data-step and proc sql in one step:
proc sql noprint;
select catx('.', libname, memname)
into :memnames separated by ' '
from sashelp.vtable
where libname = 'libname' and memname contains 'CUST_201503'
;
quit;
I knew there must be a way to do it in one step!
Thank you.
I want to pick the weekly data in YYYMMDD suffix but not the monthly data in YYYYMM suffix.
I could not use wildcard set statement.
data cust_weekly;
set cust_201503:;
run;
Hence i will use yours:
proc sql noprint;
select catx('.', libname, memname)
into :memnames separated by ' '
from sashelp.vtable
where libname = 'libname' and memname contains 'CUST_201503' and length(memname) = 20
;
quit;
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.