Loop in dates for %macro function

[kajal_30]

Hi all ,

I am trying to run the same programs using different dates. Is it possible to run this program using 30 dates at once putting those dates under one macro variable as below? if there is a better way to do this please let me know.

proc sql;
select curr_dt  into : to_dt from customer ;
quit;
 
%put &to_dt ;
logs:
202401
202402
202403
202404
202405
202407
202408
202409
202406
%macro (dt );

proc sql;
create table sample_&dt. as 
select * from data where date = &dt. 
;
quit;

%mend ( &to_dt);

Thanks

Kajal

[PaigeMiller]

---

@kajal_30 wrote:

I am trying to run the same programs using different dates. Is it possible to run this program using 30 dates at once putting those dates under one macro variable as below? if there is a better way to do this please let me know.

---

Yes it is possible but unnecessary. Additionally, it is usually unnecessary to split up the data set named DATA into datasets by month. SAS works better on one large data set that contains everything. (Exception: if the data set is SOOOOO large that you can't work with it with all dates, then maybe data set by month makes sense).

Why is 202406 at the bottom of the list?

Please explain the problem in more detail, we need to know the big picture of what you are doing (not in terms of code but the actual problem that you are trying to solve) and please explain what you plan to do with the single month data sets once you have them.

[kajal_30]

long story short :

I have master dataset which will get updated values from the monthly tables. each value in the master table corresponds to values (to be updated in master) in different monthly table. 

so I am creating monthly tables with all the updated values and then each monthly table will update master table on the basis of each month .

Hope that helps!

Kajal

[PaigeMiller]

---

@kajal_30 wrote:

long story short :

I have master dataset which will get updated values from the monthly tables. each value in the master table corresponds to values (to be updated in master) in different monthly table. 

so I am creating monthly tables with all the updated values and then each monthly table will update master table on the basis of each month .

---

I still don't see why you need to split the data into monthly tables. Why can't you do the update on the large table with all the months in it?

[kajal_30]

Because monthly data is in all different monthly tables so fetch the data from each monthly table first and then update the master table otherwise I might have updates the table from one big table with all months.

Kajal 

[PaigeMiller]

Something like this:

data all_months;
     set sample_:;
run;
data newmaster;
    update master all_months;
    by somevariables;
run;

[kajal_30]

so there isn't a method to assign a list of values while calling a macro in sas ? 

%date_to_months(&dt_mnth.) ? 

where dt_mnth = 202312 202311 202301 202302 202304 202305 202306 202307 202308 202309 

kajal 

[PaigeMiller]

---

@kajal_30 wrote:

so there isn't a method to assign a list of values while calling a macro in sas ? 

---

I certainly didn't say that, and its not true. Stop focusing so tightly on your desire for a macro solution. I provided a possible solution which doesn't need macros or macro variables or do loops.

[Kurt_Bremser]

What is the type and format of variable date in dataset data?

What kind of analysis will you do with the extracted data?

[Tom]

I find it pretty simple to loop over a list of values a macro variable.

Change your macro to expect a list instead of just one value.

%macro mymacro(dtlist);
%local i dt;
proc sql;
%do i=1 %to %sysfunc(countw(&dtlist,%str( )));
  %let dt=%scan(&dtlist,&i,%str( ));
create table sample_&dt. as 
  select * from data where date = &dt. 
;
%end;
quit;
%mend mymacro;

Then you can call it with one or many date strings.

%mymacro(dtlist=202401)
%mymacro(dtlist=202402 202403)

To get a list of values into a single macro variable make sure to use the SEPARATED BY option on the SELECT statement.

proc sql noprint;
select distinct curr_dt
  into :curr_dates separated by ' '
  from customer 
;
quit;

You can then use that macro variable in your call to the macro.

%mymacro(dtlist=&curr_dates)

[kajal_30]

did try that but creating table only for one date not sure why 

data test;
infile datalines;
input date ;
datalines;
202401
202402
202003
202404
202505
202406
;
run;


proc sql;
select date  into : to_dt separated by ' ' from test ;
quit;
 
%put &to_dt. ;

%macro dt (list);
proc sql;
create table sample_&dt. as 
select * from cars
;
quit;
%mend dt;

%dt (list= &to_dt.) ; 

[Tom]

Your macro does not have any %DO loop.  So it cannot generate separate queries for each date value.

If your list of date values is small you could skip the macro and just put the code into the macro variable.  (has to be small since there is 64K byte limit on the length of a macro variable and the code is larger than just the date value).  Then just expand the macro variable to run the code.

proc sql noprint;
select distinct 
  catx('create table',cats('sample_',date)
      ,'as select * from cars where date=',date,';')
  into :to_dt separated by ' ' 
  from test 
;
&to_dt
quit;