DATA Step, Macro, Functions and more

Data Step Append Many Tables selected dates

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 92
Accepted Solution

Data Step Append Many Tables selected dates

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;


Accepted Solutions
Solution
‎04-02-2015 04:24 AM
Super Contributor
Posts: 345

Re: Data Step Append Many Tables selected dates

Posted in reply to KurtBremser

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;

View solution in original post


All Replies
Super User
Posts: 7,866

Re: Data Step Append Many Tables selected dates

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;

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Solution
‎04-02-2015 04:24 AM
Super Contributor
Posts: 345

Re: Data Step Append Many Tables selected dates

Posted in reply to KurtBremser

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;

Super User
Posts: 7,866

Re: Data Step Append Many Tables selected dates

Posted in reply to andreas_lds

I knew there must be a way to do it in one step!

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Frequent Contributor
Posts: 92

Re: Data Step Append Many Tables selected dates

Posted in reply to andreas_lds

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;

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 226 views
  • 2 likes
  • 3 in conversation