Hi all, would like to ask how to set a date for me to use it in my do loop later?
if I'm using the code below (bold part) , SAS will automatically add the variable as new column in my table (I don't want this) and don't want to use drop.
From the code below, you can see that I want to set a beg_date (beginning date) and end_date(end date).
Purpose of this is to use it in my do loop later. For Ex:
%Do i=beg_yyyy_fy %to end_yyyy_fy;
%Do j=%sysfunc(ifn(&i=beg_yyyy_fy,beg_mm_fy,1)) %to %sysfunc(ifn(&i=end_yyyy_fy,end_mm_fy,12));
MY FULL CODE:
libname py201906 "\\kaiwksgh415thw5\Data\BNM_Data_Request\BNM_Qtrly_Policy_Claim_Stats\Fire\2019\201906\policy\DBF";
Data py201906.FirePolicy201906;
infile "\\kaiwksgh415thw5\Data\BNM_Data_Request\BNM_Qtrly_Policy_Claim_Stats\Fire\2019\201906\policy\Monitoring Data\Fire Policy 201906.csv" dlm=',' MISSOVER DSD lrecl=32767 firstobs=2;
informat CHDRNUM $8.;
informat CNTTYPE $3.;
informat LONGDESC $32.;
informat TRANNO 4.;
informat CURRFROM yymmdd10.;
informat ZRENNO 3.;
informat ZENDNO 3.;
informat TRD_DATE yymmdd10.;
informat TRANDATE yymmdd10.;
informat STATCODE $3.;
informat STATREASN $3.;
informat BATCTRCDE $4.;
informat BATCACTYR 4.;
informat BATCACTMN 2.;
informat COWNNUM 8.;
informat LSURNAME $60.;
informat CCDATE yymmdd10.;
informat CRDATE yymmdd10.;
informat OCCDATE yymmdd10.;
informat DTECAN yymmdd10.;
informat AGNTNUM $8.;
informat AGTYPE 3.;
informat LSURNAME01 $60.;
informat ZLIFAGNT $8.;
informat CHDRSTCDE 3.;
informat MPLNUM 8.;
informat REPNUM 8.;
informat ZREPOLNO $16.;
informat CAMPAIGN $16.;
informat RNLDURN 3.;
informat CNTBRANCH $3.;
informat CHDRSTCDB $3.;
informat CHDRSTCDC $3.;
informat CLTSEX $3.;
informat MARRYD $3.;
informat SECUITYNO $25.;
informat CLTDOB yymmdd10.;
informat DTEATT yymmdd10.;
informat RSKNO 3.;
informat RSKTYP $3.;
informat RATFLG $3.;
informat STATECDE $3.;
informat LOCREG $10.;
informat PCODE $10.;
informat ZOCUPY 3.;
informat ZOCUPD $60.;
informat RATECDE $6.;
informat ZRATEDES $50.;
informat RATEBAS01 $1.;
informat RATEBAS02 $1.;
informat SCONST $3.;
informat ZCNSTDES $8.;
informat CONSYEAR 4.;
informat ZFIRPCDE $3.;
informat BASRATE01 6.;
informat SNO 3.;
informat INTCDE 3.;
informat COVER_DESC $30.;
informat ZITEMSI 10.;
informat ZPRFLG $2.;
informat PREMRATE 10.;
informat GPMPLIER 6.;
informat LOAD 6.;
informat PPREM 6.;
informat PRC $3.;
informat PREMCL $3.;
informat ZADSI01 2.;
informat ZADRTE01 2.;
informat ZADPRE01 2.;
informat ZLTAPER01 2.;
informat ZLTAAMT01 2.;
informat TOTFSI 10.;
informat FTOTPRE 10.;
informat DTETER yymmdd10.;
informat EXTR01 10.;
informat EXTR03 8.;
informat EXTR02 3.;
informat APREMDUE 10.;
informat INDISC 4.;
informat GTGRNR $3.;
informat ZCMPC 4.;
informat ZCMEXTR 8.;
informat EXTR06 8.;
informat G_PREM01 6.;
informat G_COMM01 8.;
informat Q_PREM01 3.;
informat Q_COMM01 3.;
informat S_PREM01 3.;
informat S_COMM01 3.;
informat F_PREM01 8.;
informat F_COMM01 8.;
informat X_PREM01 3.;
informat X_COMM01 3.;
format CHDRNUM $8.;
format CNTTYPE $3.;
format LONGDESC $32.;
format TRANNO 4.;
format CURRFROM yymmdd10.;
format ZRENNO 3.;
format ZENDNO 3.;
format TRD_DATE yymmdd10.;
format TRANDATE yymmdd10.;
format STATCODE $3.;
format STATREASN $3.;
format BATCTRCDE $4.;
format BATCACTYR 4.;
format BATCACTMN 2.;
format COWNNUM 8.;
format LSURNAME $60.;
format CCDATE yymmdd10.;
format CRDATE yymmdd10.;
format OCCDATE yymmdd10.;
format DTECAN yymmdd10.;
format AGNTNUM $8.;
format AGTYPE 3.;
format LSURNAME01 $60.;
format ZLIFAGNT $8.;
format CHDRSTCDE 3.;
format MPLNUM 8.;
format REPNUM 8.;
format ZREPOLNO $16.;
format CAMPAIGN $16.;
format RNLDURN 3.;
format CNTBRANCH $3.;
format CHDRSTCDB $3.;
format CHDRSTCDC $3.;
format CLTSEX $3.;
format MARRYD $3.;
format SECUITYNO $25.;
format CLTDOB yymmdd10.;
format DTEATT yymmdd10.;
format RSKNO 3.;
format RSKTYP $3.;
format RATFLG $3.;
format STATECDE $3.;
format LOCREG $10.;
format PCODE $10.;
format ZOCUPY 3.;
format ZOCUPD $60.;
format RATECDE $6.;
format ZRATEDES $50.;
format RATEBAS01 $1.;
format RATEBAS02 $1.;
format SCONST $3.;
format ZCNSTDES $8.;
format CONSYEAR 4.;
format ZFIRPCDE $3.;
format BASRATE01 6.;
format SNO 3.;
format INTCDE 3.;
format COVER_DESC $30.;
format ZITEMSI 10.;
format ZPRFLG $2.;
format PREMRATE 10.;
format GPMPLIER 6.;
format LOAD 6.;
format PPREM 6.;
format PRC $3.;
format PREMCL $3.;
format ZADSI01 2.;
format ZADRTE01 2.;
format ZADPRE01 2.;
format ZLTAPER01 2.;
format ZLTAAMT01 2.;
format TOTFSI 10.;
format FTOTPRE 10.;
format DTETER yymmdd10.;
format EXTR01 10.;
format EXTR03 8.;
format EXTR02 3.;
format APREMDUE 10.;
format INDISC 4.;
format GTGRNR $3.;
format ZCMPC 4.;
format ZCMEXTR 8.;
format EXTR06 8.;
format G_PREM01 6.;
format G_COMM01 8.;
format Q_PREM01 3.;
format Q_COMM01 3.;
format S_PREM01 3.;
format S_COMM01 3.;
format F_PREM01 8.;
format F_COMM01 8.;
format X_PREM01 3.;
format X_COMM01 3.;
input
CHDRNUM $
CNTTYPE $
LONGDESC $
TRANNO
CURRFROM
ZRENNO
ZENDNO
TRD_DATE
TRANDATE
STATCODE $
STATREASN $
BATCTRCDE $
BATCACTYR
BATCACTMN
COWNNUM
LSURNAME $
CCDATE
CRDATE
OCCDATE
DTECAN
AGNTNUM $
AGTYPE
LSURNAME01 $
ZLIFAGNT $
CHDRSTCDE
MPLNUM
REPNUM
ZREPOLNO $
CAMPAIGN $
RNLDURN
CNTBRANCH $
CHDRSTCDB $
CHDRSTCDC $
CLTSEX $
MARRYD $
SECUITYNO $
CLTDOB
DTEATT
RSKNO
RSKTYP $
RATFLG $
STATECDE $
LOCREG $
PCODE $
ZOCUPY
ZOCUPD $
RATECDE $
ZRATEDES $
RATEBAS01 $
RATEBAS02 $
SCONST $
ZCNSTDES $
CONSYEAR
ZFIRPCDE $
BASRATE01
SNO
INTCDE
COVER_DESC $
ZITEMSI
ZPRFLG $
PREMRATE
GPMPLIER
LOAD
PPREM
PRC $
PREMCL $
ZADSI01
ZADRTE01
ZADPRE01
ZLTAPER01
ZLTAAMT01
TOTFSI
FTOTPRE
DTETER
EXTR01
EXTR03
EXTR02
APREMDUE
INDISC
GTGRNR $
ZCMPC
ZCMEXTR
EXTR06
G_PREM01
G_COMM01
Q_PREM01
Q_COMM01
S_PREM01
S_COMM01
F_PREM01
F_COMM01
X_PREM01
X_COMM01;
if _ERROR_ then call symputx('_EFIERR_',1);
run;
libname py201904 "\\kaiwksgh415thw5\Data\BNM_Data_Request\BNM_Qtrly_Policy_Claim_Stats\Fire\2019\201904\policy\DBF";
Data py201904.FirePolicy201904;
Set py201904.FirePolicy201904;
libname py201905 "\\kaiwksgh415thw5\Data\BNM_Data_Request\BNM_Qtrly_Policy_Claim_Stats\Fire\2019\201905\policy\DBF";
Data py201905.FirePolicy201905;
Set py201905.FirePolicy201905;
Data py201906.RiskProfile_Fire_201904_201906;
Set
py201904.FirePolicy201904
py201905.FirePolicy201905
py201906.FirePolicy201906;
run;
proc sql;
create table query as
select distinct chdrnum,trandate,batctrcde,batcactyr,batcactmn,tranno,rskno,totfsi,ratecde,cnttype,rsktyp,premcl,currfrom,EXTR01,EXTR03,EXTR02,ZCMEXTR,EXTR06,G_PREM01,Q_PREM01,S_PREM01,F_PREM01,X_PREM01,G_COMM01,Q_COMM01,S_COMM01,F_COMM01,X_COMM01
from py201906.RiskProfile_Fire_201904_201906
where statcode="IF" or statcode="CA";
quit;
proc sql;
create table py201906.RiskProfFire_201904_201906_group as
select *
from query
where premcl^="";
run;
Data py201906.RiskProfFire_201904_201906_group;
Set py201906.RiskProfFire_201904_201906_group;
if month(currfrom)=12 then
effyyyy_fy=year(currfrom)+1;
else
effyyyy_fy=year(currfrom);
if month(currfrom)=12 then
effmth_fy=1;
else
effmth_fy=month(currfrom)+1;
/*********************/
if batcactmn=12 then
batyr=batcactyr+1;
else
batyr=batcactyr;
/*********************/
if batcactyr<=2014 then
acyear=batyr;
else
acyear=batcactyr;
/*********************/
if batcactmn=12 then
batmth=1;
else
batmth=batcactmn+1;
/*********************/
if batcactyr<=2014 then
acmonth=batmth;
else
acmonth=batcactmn;
drop batyr;
drop batmth;
if (effyyyy_fy*100+effmth_fy)<(acyear*100+acmonth) then
post_yy_fy=acyear;
else
post_yy_fy=effyyyy_fy;
if (effyyyy_fy*100+effmth_fy)<(acyear*100+acmonth) then
post_mm_fy=acmonth;
else
post_mm_fy=effmth_fy;
/*****************************************************************************/
length test1 $4;
test1=left(ratecde);
length test2 $2;
test2=left(ratecde);
drop test1;
drop test2;
if totfsi<=10000000 then
si_band='SI1';
else if totfsi>=10000001 and totfsi<=50000000 then
si_band='SI2';
else if totfsi>=50000001 and totfsi<=300000000 then
si_band='SI3';
else if totfsi>=300000001 then
si_band='SI4';
/************************************************************************************/
if ratecde^="1000" and ratecde^="COIN" and ratecde^="" then do;
if rsktyp in ('FCL','FCZ','FLP') then
cov_type='Conloss';
else if rsktyp in ('FAZ','FDA','FIA') then
cov_type='IAR';
else if test1 in ('4006','4008') then
cov_type='HH';
else if test1 in ('4001','4005') then
cov_type='HO';
else if test2=10 then
cov_type='MD1';
else if test2=11 then
cov_type='MD2';
else if test2=12 then
cov_type='MD3';
else if test2=14 then
cov_type='MD4';
else if test2=29 then
cov_type='MD5';
else
cov_type='MD6';
end;
/**************************************************/
if ratecde="1000" or ratecde="COIN" or ratecde="" then do;
if rsktyp in ('FHO','FHW','FHZ') then
cov_type='HH';
else if rsktyp in ('FHH','FHI','FHY') then
cov_type='HO';
else if rsktyp in ('FCL','FCZ','FLP') then
cov_type='Conloss';
else if rsktyp in ('FAZ','FDA','FIA') then
cov_type='IAR';
else if rsktyp in ('FDP','FFD','FFZ','FHX','FMD','FMP') then
cov_type='MD1';
end;
run;
libname py201903 "\\kaiwksgh415thw5\Data\BNM_Data_Request\BNM_Qtrly_Policy_Claim_Stats\Fire\2019\201903\policy\DBF";
Data py201903.riskproffire_201501_201903_group;
Set py201903.riskproffire_201501_201903_group;
proc sql noprint;
create table query2 as
select *
from py201903.RiskProfFire_201501_201903_group
where post_yy_fy*100+post_mm_fy>=201707 and post_yy_fy*100+post_mm_fy<=201907;
proc sql noprint;
create table query3 as
select *
from py201906.RiskProfFire_201904_201906_group
where post_yy_fy*100+post_mm_fy>=201707 and post_yy_fy*100+post_mm_fy<=201907;
proc sql;
create table py201903.RskProFir_201706_201906_post_EP as
select * from query2
union all
select * from query3;
run;
Data py201903.RskProFir_201706_201906_post_EP;
Set py201903.RskProFir_201706_201906_post_EP;
beg_date = INPUT(PUT(20181201,8.),YYMMDD10.);
FORMAT beg_date YYMMDD10.;
end_date = INPUT(PUT(20190630,8.),YYMMDD10.);
FORMAT end_date YYMMDD10.;
if month(beg_date)=12 then
beg_yyyy_fy=year(beg_date)+1;
else
beg_yyyy_fy=year(beg_date);
if month(beg_date)=12 then
beg_mm_fy=1;
else
beg_mm_fy=month(beg_date)+1;
if month(end_date)=12 then
end_yyyy_fy=year(end_date)+1;
else
end_yyyy_fy=year(end_date);
if month(end_date)=12 then
end_mm_fy=1;
else
end_mm_fy=month(end_date)+1;
run;
PLEASE POST YOUR CODE WITH THE "LITTLE RUNNING MAN" ICON, AND GIVE IT AT LEAST SOME VISUAL FORMATTING.
I won' even TRY to read that eye-sore.
Macro code and data step code do not mingle. Macro variables won't show up in datasets on their own.
And I do not see where you use that macro do loop in your code.
A line like this
%Do i=beg_yyyy_fy %to end_yyyy_fy;
will always cause an ERROR, as the text(!) beg_yyyy_fy (or end_yyyy_fy) is not numeric. Either you have macro variables (which must be addressed with a &), or literal numeric values.
Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.
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.