BookmarkSubscribeRSS Feed
suresh123
Calcite | Level 5

 

 

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 begin=20170101;

%let end =20170501;

 

and create datasets based on begin and end using loop. Is it possible to do that.

17 REPLIES 17
PeterClemmensen
Tourmaline | Level 20

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.

suresh123
Calcite | Level 5
Splitting to see each month reports
ChrisNZ
Tourmaline | Level 20

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.

 

suresh123
Calcite | Level 5
I dont want to use %macro or call execute....
PeterClemmensen
Tourmaline | Level 20

You post a macro yourself in your question?

suresh123
Calcite | Level 5
%let beg="01jan2000"d;
%let end="01jan2001"d;
%macro Test;
%do date=&beg. %to &end.;
proc sql;
create table IPw_&date. as
select *
from sample
where date=&date. quit;
%end;
%mend;
%Test;


Tom
Super User Tom
Super User

@suresh123 wrote:
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?

suresh123
Calcite | Level 5
Can you elaborate this %include code in my scenario
suresh123
Calcite | Level 5
So what iam looking is, I dont want to enter the dates manually through %pull(20170101,20170501). Its need to pick from %let macro
Tom
Super User Tom
Super User

@suresh123 wrote:
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)
PeterClemmensen
Tourmaline | Level 20

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;
suresh123
Calcite | Level 5
Is there any chance we can do it using do loop to create multiple datatsets between the dates
PeterClemmensen
Tourmaline | Level 20

What do you want those data sets to contain?

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 17 replies
  • 1281 views
  • 2 likes
  • 5 in conversation