topic Re: Run macro on values from data set in SAS Programming

Run macro on values from data set

Ronein — Mon, 22 Jan 2024 11:12:18 GMT

Hello

What is the way to change the code and run the macro for the values appear in data set.(Want_Dates_YYMM_To_Run_Macro)

Here is the dates I want to run the macro on .

note- The source data sets are with YYMM name


%let FROM_YYMM=1712;
%let Till_YYMM=2312;


data _null_;
date_start=mdy(mod(&FROM_YYMM,100),1,floor(&FROM_YYMM/100));
date_end=mdy(mod(&Till_YYMM,100),1,floor(&Till_YYMM/100));
call symputx('date_start',put(date_start,best.));
call symputx('date_end',put(date_end,best.));
format date_start date_end  date9.;
run;

data _series_End_Of_Quarters;
date=&date_start.;
end_date=&date_end.;
YYMM=input(put(date,yymmn4.),best.);
format date end_date date9.;
do while (date<=end_date);
output;
date=intnx('month', date, 3, 's');
YYMM=input(put(date,yymmn4.),best.);
end;
format date YYMMN4.;
drop date end_date;
run;

data Want_Dates_YYMM_To_Run_Macro;
set _series_End_Of_Quarters end=eof;
output;
if eof then do;
YYMM="&Till_YYMM.";
output;
end;
run;

Here is the code to run the macro by typing each date.

My question is how to run this macro in more clever way that run on the dates in data set Want_Dates_YYMM_To_Run_Macro


%macro RRR(YYMM);
/***Split reason to 0 into multiple columns****/
%let max_wrd=;
proc sql noprint;
select max(countw(teur_rej_type,'|')) into :max_wrd trimmed
from  LABTRET.ABT_CS_&YYMM.L
where groupk=0 AND lakoach ne 0 AND hativa in (833,703,860) AND  mekushar=0 AND  niz<=5000000
;
quit;
%put max_wrd=&max_wrd;


data ABT_0(drop=_:);
set LABTRET.ABT_CS_&YYMM.L(Where=(groupk=0 AND lakoach ne 0 AND hativa in (833,703,860) AND  mekushar=0 AND  niz<=5000000));
array model_[&max_wrd] $50 ;
do _i = 1 to dim(model_);
model_[_i] = scan(teur_rej_type,_i,'|');
if missing(model_[_i]) then leave;
end;
run;


%macro sset;
%do j=1 %to &max_wrd.;
ABT_0(Keep=Lakoach niz hativa model_&j. rename=(model_&j.=Siba0)) 
%end;
%mend sset;
%put %sset;

Data ABT_0_Long(Where=(Siba0 ne ''));
set %sset;
Run;


data ABT_0_Long2;
length _Siba_0_ $100.;
set ABT_0_Long;
if find(compress(Siba0),'מטי','i') then _Siba_0_='חסרי מטי';
else if find(compress(Siba0),'בעלות','i') then _Siba_0_='סמלי בעלות';
else if find(compress(Siba0),'אופי','i') then _Siba_0_='אופי חשבון';
else if find(compress(Siba0),'רצף','i') then _Siba_0_='אי רצף 6 חודשים';
else if find(compress(Siba0),'חדש','i') then _Siba_0_='לקוח חדש';
else if find(compress(Siba0),'640','i') then _Siba_0_='סניף 640';
else if find(compress(Siba0),'חשיפה','i') then _Siba_0_='רף חשיפה';
else if find(compress(Siba0),'קטינים','i') then _Siba_0_='קטינים/לא_פעיל_פפר';
else if find(compress(Siba0),'קיים','i') then _Siba_0_='לא_קיים';
Run;
 
proc sort data=ABT_0_Long2;
by lakoach _Siba_0_;
Run;

data ABT_0_Wide;
length calc_Shirshur_Siba0 $100.;
do until (last.lakoach);
set ABT_0_Long2;
by lakoach;
calc_Shirshur_Siba0=catx('||',calc_Shirshur_Siba0,_Siba_0_);
end;
Drop _Siba_0_  Siba0;
run;
 

proc sql;
create table _pelet1_&YYMM. as
select hativa,teur_rej_type,
       count(*) as nr_laks&YYMM.,
	   sum(niz)/1000000 as niz&YYMM. format=8.1
from ABT_0
group by hativa,teur_rej_type
;
quit;

proc sql;
create table _pelet2_&YYMM. as
select hativa,calc_Shirshur_Siba0,
       count(*) as nr_laks&YYMM.,
	   sum(niz)/1000000 as niz&YYMM. format=8.1
from ABT_0_Wide
group by hativa,calc_Shirshur_Siba0
;
quit;
 
 
proc sql;
create table _pelet3_Univaraite_Analysis_&YYMM. as
select hativa,_Siba_0_,
       count(distinct lakoach) as nr_laks&YYMM.,
	   sum(niz)/1000000 as niz&YYMM. format=8.1
from ABT_0_Long2
group by hativa,_Siba_0_
;
quit;
%mend RRR;
%RRR(1803)
%RRR(1806)
%RRR(1809)
%RRR(1812)
%RRR(1903)
%RRR(1906)
%RRR(1909)
%RRR(1912)
%RRR(2003)
%RRR(2006)
%RRR(2009)
%RRR(2012)
%RRR(2103)
%RRR(2106)
%RRR(2109)
%RRR(2112)
%RRR(2203)
%RRR(2206)
%RRR(2209)
%RRR(2212)
%RRR(2303)
%RRR(2306)
%RRR(2309)
%RRR(2312)

Re: Run macro on values from data set

PaigeMiller — Mon, 22 Jan 2024 12:53:55 GMT

The data step that creates data _series_End_Of_Quarters contains a loop that puts values into a DATA set. Instead, make this a macro loop, and create a macro variable with the quarter information, and suppose this macro variable is named &QUARTER, then simply include %RRR(&quarter) in the macro loop. This data set is not needed here.

Use CALL EXECUTE (examples of calling a macro via CALL EXECUTE are given)

Re: Run macro on values from data set

s_lassen — Mon, 22 Jan 2024 12:57:01 GMT

The simplest way may be to create a temporary file, and write the code to that:

filename tempsas temp;

data _null_;
  set Want_Dates_YYMM_To_Run_Macro;
  put '%RRR(' YYMM ');';
run;

%include tempsas / source2;

With this method is its easy to open the generated SAS code in an editor and check the generated code before submitting it (the whole file is submitted in the %INCLUDE statement).

Re: Run macro on values from data set

Ronein — Mon, 22 Jan 2024 14:36:36 GMT

May you show code please ?

Re: Run macro on values from data set

PaigeMiller — Mon, 22 Jan 2024 14:47:53 GMT

Please give it a try, show us your code.

For CALL EXECUTE, I linked to documentation with examples.

Re: Run macro on values from data set

yabwon — Mon, 22 Jan 2024 15:22:36 GMT

You can also use the MacroArray package:

%array(ds=Want_Dates_YYMM_To_Run_Macro, vars=YYMM, macarray=Y)

%do_over(YYMM, phrase=%nrstr(
  %RRR(%YYMM(&_I_.))
))

Bart

P.S. Here are some instructions how to get macroArray package and where the documentation is.

/* Details about SAS Packages Framework: - https://github.com/yabwon/SAS_PACKAGES Tutorial: - https://github.com/yabwon/HoW-SASPackages MacroArray documentation: - https://github.com/SASPAC/macroarray/blob/main/macroarray.md */ /* Install SAS Packages Framework and macroaArray package. */ /* Run this only once. */ filename SPFinit url "https://bit.ly/SPFinit"; %include SPFinit; filename SPFinit clear; filename packages "</your/directory/for/packages/>"; %installPackage(SPFinit macroArray) /* Run this in your SAS Serrion (or add it to autoexec). */ /* Enable framework and load package */ filename packages "</your/directory/for/packages/>"; %include packages(SPFinit.sas); %loadPackage(macroArray)

Re: Run macro on values from data set

Tom — Mon, 22 Jan 2024 15:44:08 GMT

Very simple:

%let FROM_YYMM=1712;
%let Till_YYMM=2312;

data _null_;
  date_start=input("20&FROM_YYMM.01",yymmdd8.);
  date_end  =input("20&Till_YYMM.01",yymmdd8.);
  do offset=0 to intck('month',date_start,date_end) by 3;
    date=intnx('month',date_start,offset);
    call execute(cats('%nrstr(%rrr)(',put(date,yymmn4.),')'));
  end;
run;

Let's try it with a dummy macro that does nothing to make sure the calls are generated.

375  %macro rrr(yymm);
376
377  %mend;
378
379  data _null_;
380    date_start=input("20&FROM_YYMM.01",yymmdd8.);
381    date_end  =input("20&Till_YYMM.01",yymmdd8.);
382    do offset=0 to intck('month',date_start,date_end) by 3;
383      date=intnx('month',date_start,offset);
384      call execute(cats('%nrstr(%rrr)(',put(date,yymmn4.),')'));
385    end;
386  run;

NOTE: DATA statement used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds


NOTE: CALL EXECUTE generated line.
1   + %rrr(1712)
2   + %rrr(1803)
3   + %rrr(1806)
4   + %rrr(1809)
5   + %rrr(1812)
6   + %rrr(1903)
7   + %rrr(1906)
8   + %rrr(1909)
9   + %rrr(1912)
10  + %rrr(2003)
11  + %rrr(2006)
12  + %rrr(2009)
13  + %rrr(2012)
14  + %rrr(2103)
15  + %rrr(2106)
16  + %rrr(2109)
17  + %rrr(2112)
18  + %rrr(2203)
19  + %rrr(2206)
20  + %rrr(2209)
21  + %rrr(2212)
22  + %rrr(2303)
23  + %rrr(2306)
24  + %rrr(2309)
25  + %rrr(2312)

NOTE: Your macro %RRR looks very confused also, but you can open another ticket to get help fixing that.

Re: Run macro on values from data set

Ronein — Tue, 23 Jan 2024 11:52:28 GMT

Thanks,

I get an error

filename tempsas temp;
data _null_;
set Want_Dates_YYMM_To_Run_Macro;
put '%RRR(' YYMM ')' ;
run;
%include tempsas / source2;
/*WARNING: Physical file does not exist, /usr/local/saswork/SAS_work9A6700000E06_LINX107717A14/#LN00065.*/
/*ERROR: Cannot open %INCLUDE file TEMPSAS.*/

Re: Run macro on values from data set

Ronein — Tue, 23 Jan 2024 11:57:04 GMT

Here is the summary of the way to run the macro.

Way 3 and way 4 are not working -have error


%macro RRR(YYMM);
 Here write the queries in Macro RRR
%mend RRR;

/***Way1 to run the macro********/
/***Way1 to run the macro********/
/***Way1 to run the macro********/
/*Using call execute*/
/*Using %NRSTR() to prevent the macro from running while the call is being pushed on the stack to execute later*/
%let FROM_YYMM=1712;
%let Till_YYMM=2312;

data _null_;
date_start=input("20&FROM_YYMM.01",yymmdd8.);
date_end  =input("20&Till_YYMM.01",yymmdd8.);
do offset=0 to intck('month',date_start,date_end) by 3;
date=intnx('month',date_start,offset);
call execute(cats('%nrstr(%RRR)(',put(date,yymmn4.),')'));
end;
run;

/***Way2 to run the macro********/
/***Way2 to run the macro********/
/***Way2 to run the macro********/
%let FROM_YYMM=1712;
%let Till_YYMM=2312;

data _null_;
date_start=mdy(mod(&FROM_YYMM,100),1,floor(&FROM_YYMM/100));
date_end=mdy(mod(&Till_YYMM,100),1,floor(&Till_YYMM/100));
call symputx('date_start',put(date_start,best.));
call symputx('date_end',put(date_end,best.));
format date_start date_end  date9.;
run;
data _series_End_Of_Quarters;
date=&date_start.;
end_date=&date_end.;
YYMM=input(put(date,yymmn4.),best.);
format date end_date date9.;
do while (date<=end_date);
output;
date=intnx('month', date, 3, 's');
YYMM=input(put(date,yymmn4.),best.);
end;
format date YYMMN4.;
drop date end_date;
run;
data _Want_Dates_YYMM_To_Run_Macro;
set _series_End_Of_Quarters end=eof;
output;
if eof then do;
YYMM="&Till_YYMM.";
output;
end;
run;
 
proc sort data=_Want_Dates_YYMM_To_Run_Macro nodupkey out=Want_Dates_YYMM_To_Run_Macro;
by YYMM;
Run;

data _null_;
set Want_Dates_YYMM_To_Run_Macro;
call execute(cats('%nrstr(%RRR)(',YYMM,')'));
run;

/***Way3 to run the macro********/
/***Way3 to run the macro********/
/***Way3 to run the macro********/
/* %INCLUDE the file instead of using call execute*/
filename tempsas temp;
data _null_;
set Want_Dates_YYMM_To_Run_Macro;
put '%RRR(' YYMM ')' ;
run;
%include tempsas / source2;
/*WARNING: Physical file does not exist, /usr/local/saswork/SAS_work9A6700000E06_LINX107717A14/#LN00065.*/
/*ERROR: Cannot open %INCLUDE file TEMPSAS.*/



/***Way4 to run the macro********/
/***Way4 to run the macro********/
/***Way4 to run the macro********/
Data _Null_;
%array(ds=Want_Dates_YYMM_To_Run_Macro, vars=YYMM, macarray=Y)
%do_over(YYMM, phrase=%nrstr(%RRR(%YYMM(&_I_.))))
Run;
/*41         ;*';*";*/;quit;run;*/
/*                     ____*/
/*                     180*/
/**/
/*ERROR 180-322: Statement is not valid or it is used out of proper order.*/



/***Way5 to run the macro********/
/***Way5 to run the macro********/
/***Way5 to run the macro********/
/*%RRR(1803)*/
/*%RRR(1806)*/
/*%RRR(1809)*/
/*%RRR(1812)*/
/*%RRR(1903)*/
/*%RRR(1906)*/
/*%RRR(1909)*/
/*%RRR(1912)*/
/*%RRR(2003)*/
/*%RRR(2006)*/
/*%RRR(2009)*/
/*%RRR(2012)*/
/*%RRR(2103)*/
/*%RRR(2106)*/
/*%RRR(2109)*/
/*%RRR(2112)*/
/*%RRR(2203)*/
/*%RRR(2206)*/
/*%RRR(2209)*/
/*%RRR(2212)*/
/*%RRR(2303)*/
/*%RRR(2306)*/
/*%RRR(2309)*/
/*%RRR(2312)*/

Re: Run macro on values from data set

PaigeMiller — Tue, 23 Jan 2024 12:09:11 GMT

If there are errors, show us the ENTIRE log. Since this is a program involving macros, please turn on macro debugging options first by running the command

options mprint;

and then run your program and show us the ENTIRE log. Do not show us bits and pieces of the log.

Re: Run macro on values from data set

Ronein — Tue, 23 Jan 2024 12:21:39 GMT

Here is the code I run.

options mprint;
filename tempsas temp;
data _null_;
set Want_Dates_YYMM_To_Run_Macro;
put '%RRR(' YYMM ')' ;
run;
%include tempsas / source2;

Here is the Log

1                                                          The SAS System                            14:12 Tuesday, January 23, 2024

1          ;*';*";*/;quit;run;
2          OPTIONS PAGENO=MIN;
3          %LET _CLIENTTASKLABEL='Program';
4          %LET _CLIENTPROCESSFLOWNAME='Process Flow';
5          %LET _CLIENTPROJECTPATH='';
6          %LET _CLIENTPROJECTPATHHOST='';
7          %LET _CLIENTPROJECTNAME='';
8          %LET _SASPROGRAMFILE='';
9          %LET _SASPROGRAMFILEHOST='';
10         
11         ODS _ALL_ CLOSE;
12         OPTIONS DEV=PNG;
13         GOPTIONS XPIXELS=0 YPIXELS=0;
14         FILENAME EGSR TEMP;
15         ODS tagsets.sasreport13(ID=EGSR) FILE=EGSR
16             STYLE=HTMLBlue
17             STYLESHEET=(URL="file:///C:/Program%20Files/SASHome/SASEnterpriseGuide/7.1/Styles/HTMLBlue.css")
18             NOGTITLE
19             NOGFOOTNOTE
20             GPATH=&sasworklocation
21             ENCODING=UTF8
22             options(rolap="on")
23         ;
NOTE: Writing TAGSETS.SASREPORT13(EGSR) Body file: EGSR
24         
25         GOPTIONS ACCESSIBLE;
26         options mprint;
27         filename tempsas temp;
28         data _null_;
29         set Want_Dates_YYMM_To_Run_Macro;
ERROR: File WORK.WANT_DATES_YYMM_TO_RUN_MACRO.DATA does not exist.
30         put '%RRR(' YYMM ')' ;
31         run;

NOTE: The SAS System stopped processing this step because of errors.
NOTE: DATA statement used (Total process time):
      real time           0.03 seconds
      user cpu time       0.00 seconds
      system cpu time     0.00 seconds
      memory              205.37k
      OS Memory           21152.00k
      Timestamp           01/23/2024 02:20:35 PM
      Step Count                        4  Switch Count  0
      Page Faults                       0
      Page Reclaims                     38
      Page Swaps                        0
      Voluntary Context Switches        5
      Involuntary Context Switches      0
      Block Input Operations            0
      Block Output Operations           0
      

32         %include tempsas / source2;
WARNING: Physical file does not exist, /usr/local/saswork/SAS_work9B5D00005C37_LINX107717A13/#LN00017.
ERROR: Cannot open %INCLUDE file TEMPSAS.
33         
34         
35         GOPTIONS NOACCESSIBLE;
2                                                          The SAS System                            14:12 Tuesday, January 23, 2024

36         %LET _CLIENTTASKLABEL=;
37         %LET _CLIENTPROCESSFLOWNAME=;
38         %LET _CLIENTPROJECTPATH=;
39         %LET _CLIENTPROJECTPATHHOST=;
40         %LET _CLIENTPROJECTNAME=;
41         %LET _SASPROGRAMFILE=;
42         %LET _SASPROGRAMFILEHOST=;
43         
44         ;*';*";*/;quit;run;
45         ODS _ALL_ CLOSE;
46         
47         
48         QUIT; RUN;
49

Re: Run macro on values from data set

PaigeMiller — Tue, 23 Jan 2024 12:27:38 GMT

29         set Want_Dates_YYMM_To_Run_Macro;
ERROR: File WORK.WANT_DATES_YYMM_TO_RUN_MACRO.DATA does not exist.

Data set doesn't exist.

32         %include tempsas / source2;
WARNING: Physical file does not exist, /usr/local/saswork/SAS_work9B5D00005C37_LINX107717A13/#LN00017.
ERROR: Cannot open %INCLUDE file TEMPSAS.

File doesn't exist.

Re: Run macro on values from data set

Ronein — Wed, 24 Jan 2024 10:04:42 GMT

Sorry now data set exists and still get the error when I run

options mprint;
filename tempsas temp;
data _null_;
set Want_Dates_YYMM_To_Run_Macro;
put '%RRR(' YYMM ')' ;
run;
%include tempsas / source2;

Here is the Log

1                                                          The SAS System                            14:12 Tuesday, January 23, 2024

1          ;*';*";*/;quit;run;
2          OPTIONS PAGENO=MIN;
3          %LET _CLIENTTASKLABEL='Program';
4          %LET _CLIENTPROCESSFLOWNAME='Process Flow';
5          %LET _CLIENTPROJECTPATH='';
6          %LET _CLIENTPROJECTPATHHOST='';
7          %LET _CLIENTPROJECTNAME='';
8          %LET _SASPROGRAMFILE='';
9          %LET _SASPROGRAMFILEHOST='';
10         
11         ODS _ALL_ CLOSE;
12         OPTIONS DEV=PNG;
13         GOPTIONS XPIXELS=0 YPIXELS=0;
14         FILENAME EGSR TEMP;
15         ODS tagsets.sasreport13(ID=EGSR) FILE=EGSR
16             STYLE=HTMLBlue
17             STYLESHEET=(URL="file:///C:/Program%20Files/SASHome/SASEnterpriseGuide/7.1/Styles/HTMLBlue.css")
18             NOGTITLE
19             NOGFOOTNOTE
20             GPATH=&sasworklocation
21             ENCODING=UTF8
22             options(rolap="on")
23         ;
NOTE: Writing TAGSETS.SASREPORT13(EGSR) Body file: EGSR
24         
25         GOPTIONS ACCESSIBLE;
26         options mprint;
27         filename tempsas temp;
28         data _null_;
29         set Want_Dates_YYMM_To_Run_Macro;
30         put '%RRR(' YYMM ')' ;
31         run;

%RRR(1712 )
%RRR(1803 )
%RRR(1806 )
%RRR(1809 )
%RRR(1812 )
%RRR(1903 )
%RRR(1906 )
%RRR(1909 )
%RRR(1912 )
%RRR(2003 )
%RRR(2006 )
%RRR(2009 )
%RRR(2012 )
%RRR(2103 )
%RRR(2106 )
%RRR(2109 )
%RRR(2112 )
%RRR(2203 )
%RRR(2206 )
%RRR(2209 )
%RRR(2212 )
%RRR(2303 )
%RRR(2306 )
%RRR(2309 )
%RRR(2312 )
2                                                          The SAS System                            14:12 Tuesday, January 23, 2024

NOTE: There were 25 observations read from the data set WORK.WANT_DATES_YYMM_TO_RUN_MACRO.
NOTE: DATA statement used (Total process time):
      real time           0.00 seconds
      user cpu time       0.00 seconds
      system cpu time     0.00 seconds
      memory              461.25k
      OS Memory           27816.00k
      Timestamp           01/24/2024 12:03:04 PM
      Step Count                        49  Switch Count  0
      Page Faults                       0
      Page Reclaims                     55
      Page Swaps                        0
      Voluntary Context Switches        3
      Involuntary Context Switches      0
      Block Input Operations            0
      Block Output Operations           0
      

32         %include tempsas / source2;
WARNING: Physical file does not exist, /usr/local/saswork/SAS_work9B5D00005C37_LINX107717A13/#LN00069.
ERROR: Cannot open %INCLUDE file TEMPSAS.
33         
34         
35         GOPTIONS NOACCESSIBLE;
36         %LET _CLIENTTASKLABEL=;
37         %LET _CLIENTPROCESSFLOWNAME=;
38         %LET _CLIENTPROJECTPATH=;
39         %LET _CLIENTPROJECTPATHHOST=;
40         %LET _CLIENTPROJECTNAME=;
41         %LET _SASPROGRAMFILE=;
42         %LET _SASPROGRAMFILEHOST=;
43         
44         ;*';*";*/;quit;run;
45         ODS _ALL_ CLOSE;
46         
47         
48         QUIT; RUN;
49

Re: Run macro on values from data set

PaigeMiller — Wed, 24 Jan 2024 11:02:49 GMT

So you get the same error as before. I already explained what this means.

32         %include tempsas / source2;
WARNING: Physical file does not exist, /usr/local/saswork/SAS_work9B5D00005C37_LINX107717A13/#LN00069.
ERROR: Cannot open %INCLUDE file TEMPSAS.