topic Run macro for values in data set in SAS Programming

Run macro for values in data set

Ronein — Sat, 19 Dec 2020 16:36:02 GMT

Hello

What is the way to run macro for values appear in another data set?

In this case I want to run macro RRR for list of dates ppear in data set datesFormacro

Nnother question: How can I add the date to the summary data set?

In this case I wrote summary&date. but I am not sure if it works fine

Data RawTb;
Input ID dates value;
Cards;
1 17Jun2019'd 10
2 17Jun2019'd 20
3 21Jun2019'd 30
4 21Jun2019'd 40
5 21Jun2019'd 50
6 18May2019'd 60
7 17Jun2019'd 70
8 18May2019'd 80
9 21Jun2019'd 90
;
Run;


%macro RRR(date);
Proc sql;
Create table  summary&date.    as
Select  &date.  As date,
                 sum(value) as sum_value
From  RawTb
;
Quit;
Proc print data= summary&date.noobs;run;
%mend RRR;
/*Question1-How can I add the date to data set name?*/
/*Is it okay to write  summary&date.*/

%RRR(date=17Jun2019'd);
%RRR(date=18May2019'd);


Data datesFormacro;
Input dates;
Cards;
17Jun2019'd
18May2019'd
;
Run;
/*Question2: How can I run the macro for the dates appear in data set datesFormacro?*/

Re: Run macro for values in data set

Kurt_Bremser — Sat, 19 Dec 2020 18:06:03 GMT

Maxim 28: Macro Variables Need No Formats.

They are much easier to handle, but you need to create a human-readable value whenever needed:

%macro RRR(date);

/* prepare a human-readable date for the dataset name */
%local dsdate;
%let dsdate = %sysfunc(putn(&date.,yymmddn8.));

proc sql;
create table summary&dsdate. as
select
  &date. as date format=yymmdd10.,
  sum(value) as sum_value
from RawTb
;
quit;

proc print data=summary&dsdate. noobs;
run;

%mend RRR;

data datesFormacro;
input dates :date9.;
call execute(cats('%nrstr(%RRR(',dates,'))'));
cards;
17Jun2019
18May2019
;

Re: Run macro for values in data set

s_lassen — Mon, 21 Dec 2020 08:25:29 GMT

First problem is that you have unmatched quotes in your date values. As long as they are just data, it sort of works, but when you try to put them into unquoted macro variables, horrible things happen.

The format of a SAS date constant is 'DDMonCCYY'd, not DDMonCCYY'd (you have to have an initial quote as well).

You can actually see the problem if you look at the code here:

%RRR(date=17Jun2019'd);
%RRR(date=18May2019'd);

- if you look at it in a SAS editor (or in your post after posting), you will see that the color coding shows that the text from the first quote to the last one is all considered as quoted text, so what you have is actually a single macro call like

%RRR(date=17Jun2019'd); %RRR(date=18May2019'd);

In other words, the macro is called one single time with the date parameter having the value

"17Jun2019'd); %RRR(date=18May2019'd" - the text inside the single quotes does not get resolved by the macro processor.

As @Kurt_Bremser already suggested, you should make your data set with SAS date values, not (semi-)quoted strings, e.g.:

Data RawTb;
informat dates date9.;
format dates date9.;
Input ID dates value;
Cards;
1 17Jun2019 10
2 17Jun2019 20
3 21Jun2019 30
4 21Jun2019 40
5 21Jun2019 50
6 18May2019 60
7 17Jun2019 70
8 18May2019 80
9 21Jun2019 90
;Run;

Same in the macro you should use formatted date values, so that you get a meaningful name for the output:

%macro RRR(date);
Proc sql;
Create table  summary&date.    as
Select  "&date."d  As date format=date9.,
                 sum(value) as sum_value
From  RawTb
;
Quit;
Proc print data= summary&date.noobs;run;
%mend RRR;

%RRR(date=17Jun2019);

Your datesFormacro table should be created likewise:

Data datesFormacro;
informat dates date9.;
format dates date9.;
Input dates;
Cards;
17Jun2019
18May2019
;
Run;

You can then use CALL Execute to generate the call, or you can write the code to a temporary file and %INCLUDE it. A third possibility (as you are already into SQL) is this:

proc sql noprint;
  select cats('%RRR(',put(dates,date9.),')') into :code separated by ';'
  from datesFormacro;
quit;
&code;

I assume that what you really want is the sum for that date, not the same total sum for all dates. But that should be easy to fix inside the macro, just add

where dates="&date"d

too the SQL expression.

But to be honest, what is the point of having a dataset for each date? You can just sum for each date in SQL:

proc sql;
  create tabe summary as select dates as date,sum(value) as sum_value
  from RawTb
  where dates in(select dates from datesFormacro)
  group by date;
quit;

and get rid of all the macro stuff.