topic Re: Loop between two macro variables in SAS Programming

Loop between two macro variables

suresh123 — Sat, 28 Apr 2018 09:17:28 GMT

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.

Re: Loop between two macro variables

PeterClemmensen — Sat, 28 Apr 2018 09:23:26 GMT

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.

Re: Loop between two macro variables

suresh123 — Sat, 28 Apr 2018 09:25:07 GMT

Splitting to see each month reports

Re: Loop between two macro variables

ChrisNZ — Sat, 28 Apr 2018 09:31:38 GMT

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.

Re: Loop between two macro variables

ChrisNZ — Sat, 28 Apr 2018 09:32:50 GMT

And I agree with @PeterClemmensen btw.

Re: Loop between two macro variables

PeterClemmensen — Sat, 28 Apr 2018 09:47:29 GMT

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;

Re: Loop between two macro variables

suresh123 — Sat, 28 Apr 2018 10:33:35 GMT

Is there any chance we can do it using do loop to create multiple datatsets between the dates

Re: Loop between two macro variables

PeterClemmensen — Sat, 28 Apr 2018 10:35:04 GMT

What do you want those data sets to contain?

Re: Loop between two macro variables

suresh123 — Sat, 28 Apr 2018 10:41:40 GMT

whatever it there in datatset acct. So what i am trying to do is give start and end date as macro variable and pull records from from acct dataset and create seperate datasets for each month between start and end dates

Re: Loop between two macro variables

suresh123 — Sat, 28 Apr 2018 12:03:54 GMT

I dont want to use %macro or call execute....

Re: Loop between two macro variables

PeterClemmensen — Sat, 28 Apr 2018 14:25:41 GMT

You post a macro yourself in your question?

Re: Loop between two macro variables

suresh123 — Sat, 28 Apr 2018 14:31:22 GMT

%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;

Re: Loop between two macro variables

Tom — Sat, 28 Apr 2018 16:40:53 GMT

@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?

Re: Loop between two macro variables

Patrick — Sun, 29 Apr 2018 00:46:24 GMT

@suresh123

You wrote earlier "Splitting to see each month reports"

Can you please let us know the whole story? What do you intend to do with these monthly data sets?

If all this splitting data is only to support monthly reporting then there is a good chance that you don't need to split the data at all.

Using By Group processing allows for creation of multiple reports with a single Proc call, is easier to code, maintain and also processes more efficiently.

Sample code to illustrate the concept:

data have;
  do i=1 to 20;
    date='01Jan2018'd+i-1;
    output;
  end;
  format date date9.;
  stop;
run;

%let startDt=05Jan2018;
%let endDt  =10Jan2018;

options nobyline;
title "Report for Date: #BYVAL(date)";
proc print data=have noobs;
  by date;
  var i date;
  where date between "&startDt"d and "&endDt"d;
run;

Re: Loop between two macro variables

suresh123 — Sun, 29 Apr 2018 03:41:21 GMT

Can you elaborate this %include code in my scenario

Re: Loop between two macro variables

suresh123 — Sun, 29 Apr 2018 04:16:24 GMT

So what iam looking is, I dont want to enter the dates manually through %pull(20170101,20170501). Its need to pick from %let macro

Re: Loop between two macro variables

Tom — Sun, 29 Apr 2018 14:21:21 GMT

@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)

Re: Loop between two macro variables

suresh123 — Thu, 03 May 2018 05:57:09 GMT

What if macros are given quotes

%let begin=‘20170101’;
%let end =‘20170501’;

How does loop works ?