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)
Click image to register for webinar
