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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.