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)
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.
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.
OR
Use CALL EXECUTE (examples of calling a macro via CALL EXECUTE are given)
Please give it a try, show us your code.
For CALL EXECUTE, I linked to documentation with examples.
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).
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.*/
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)
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.
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)*/
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.
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
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.
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
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.
Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
Register now!
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.
Ready to level-up your skills? Choose your own adventure.