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;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.