04-28-2018 05:17 AM
I created below macro to generate few datasets based on date macro.
%macro pull(date); proc sql; create table new&date as select * from acct where date=&date.; quit; %mend;
So if i want to create dataset for 20170101 20170201 20170301 20170401 20170501, all i can do is use below macro
%macro pull(20170101) %macro pull(20170201) %macro pull(20170301) %macro pull(20170401) %macro pull(20170501)
What i am planning now is create two macro variables
%let end =20170501;
and create datasets based on begin and end using loop. Is it possible to do that.
04-28-2018 05:23 AM
1. Why do you want to split up your data like this?
2. You don't need a macro to do this. Do a google search for CALL EXECUTE Examples. That will get you in the right direction.
04-28-2018 05:31 AM
This should help:
%macro loop(startdt,enddt); %local date; %do date=&startdt. %to &enddt.; %if %substr(&date.,5,2)=13 %then %let date=%eval(&date-12+100); proc sql; create table new&date. as select * from acct where date=&date.; quit; %end; %mend; %loop(201701,201703);
Using intnx() is the proper way, but this is easier to understand and works just as well.
04-28-2018 10:31 AM
04-28-2018 12:40 PM - edited 04-28-2018 12:40 PM
I dont want to use %macro or call execute....
If is difficult to generate dataset names without using some type of code generation. The third choice for code generation is to use a data step to write the code to a text file and then %include the file.
filename code temp; data _null_; set have ; by date ; where date between &start and &end ; file code ; if first.date then put .......... ; run; %include code;
A fourth way to dynamically generate dataset names is to use HASH objects. But hash objects are limited to amount of data that can be stored in memory.
WHY do you need separate dataset? What value is being added by splitting the data into many separate files?
04-29-2018 10:21 AM
So what iam looking is, I dont want to enter the dates manually through %pull(20170101,20170501). Its need to pick from %let macro
Are you just talking about doing something like this?
%let begin=20170101; %let end =20170501; %pull(&begin,&end)
04-28-2018 05:47 AM
A non macro way
proc sql; create table dates as select distinct date from sashelp.stocks where "01jan2000"d <= date <= "01jan2001"d; quit; data callstack; set dates; code=cats("data Stocks_", put(date, date9.), "; set sashelp.stocks; where date= ", date, ";run;"); call execute(code); run;