set a date for me to use it in my do loop later (not adding variable column)

Kayla_Tan222 — Tue, 23 Jul 2019 07:08:03 GMT

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;

topic Re: set a date for me to use it in my do loop later (not adding variable column) in SAS Programming

set a date for me to use it in my do loop later (not adding variable column)

Re: set a date for me to use it in my do loop later (not adding variable column)