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

Kayla_Tan222 · Posted 07-23-2019 03:03 AM

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;

Kurt_Bremser · Posted 07-23-2019 03:43 AM

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.

Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
The macro for direct download as ZIP
How to post code
Please vote for Provide Sequential Search Capability for Hash Objects
How to deal with locked files on UNIX

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)

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)

Click image to register for webinar

Classroom Training Available!