topic Re: Stuck on adding columns with calculated values to table (macro vars) in SAS Programming

Stuck on adding columns with calculated values to table (macro vars)

jbear — Thu, 25 Jul 2013 21:55:05 GMT

Hello...

I am having trouble figuring out an approach for this problem:

(1) User chooses end of month date and period to generate date macro vars:

%Let Begin = 7/31/2013;

%Let Preiods=10;

data datesexp (keep=futdates futdatesday)

do i=0 to &Periods;

futdates=intnx('month',input("&Periods",mmddyy10.),i,'end');

futdatesday=day(intnx('month',input("&Periods",mmddyy10.),i,'end'));

call symputx(cats('DtVar',put(i+1,8.)),put(futdates,mmddyy10.));

call symputx(cats('DtVarDay',put(i+1,8.)),put(futdatesday,2.));

format futdates mmddyy10. futdatesday 2.;

output;

end;

run;

(2) futdates1-futdates10 (31, 31, etc.) were stored

(3) existing data table has effective day of contract and I want to compute (1-(effday/&futdatesdayN) and add a column with the value (example):

Contrt	Name	Effective Dt	EffectiveDtDay	Paid Amt	FutDateDayPct1	FutDateDayPct2	…	FutDateDayPct10
0000001	Joe	10/5/2011	5	10	1-(5/31)	1-(5/31)	…	1-(5/30)
0000002	Mary	11/8/2010	8	12	1-(8/31)	1-(8/31)	…	1-(8/30)

Macro variable values:

futdates1	7/31/2013	futdatesday1	31
futdates2	8/31/2013	futdatesday2	31
futdates10	4/30/2014	futdatesday10	30

I was thinking an array, but can't figure it out. Is there an easier approach?

Thanks,

jbear

Re: Stuck on adding columns with calculated values to table (macro vars)

Reeza — Thu, 25 Jul 2013 22:25:52 GMT

I don't quite follow your calculations, but for data where the number of period may vary I like to keep my data long and then flip it using proc transpose at the end. That way the number of periods can vary without my code changing too much.

Alternatively an array isn't too much work, but like I said, I don't quite follow what you're doing.

Re: Stuck on adding columns with calculated values to table (macro vars)

jbear — Thu, 25 Jul 2013 23:02:58 GMT

Here's a better example...:

&y1=1

&y2=12

&y3=15

Old table

Obs x

1 2

2 3

3 4

New table

Obs x NewCol1 (x-&y1) NewCol2 (x-&y2) NewCol3 (x-&y3)

1 2 1 -10 -13

2 3 2 -9 -12

3 4 3 -8 -11

Re: Stuck on adding columns with calculated values to table (macro vars)

Linlin — Fri, 26 Jul 2013 01:11:15 GMT

%let y1=1;

%let y2=12;

%let y3=15;

data old;

input x @@;

cards;

2 3 4

;

data new;

set old;

newcol1=x-&y1;

newcol2=x-&y2;

newcol3=x-&y3;

proc print;run;

Re: Stuck on adding columns with calculated values to table (macro vars)

jbear — Fri, 26 Jul 2013 14:36:55 GMT

Hi Linlin...that seems to work, I guess my question would be, how to approach it if there is an unspecified number of columns to be created, since the macro vars are generated depending on the user's choice.

There would be 10 &futdatesday macro vars:

%Let Begin = 7/31/2013;

%Let Preiods=10;

data datesexp (keep=futdates futdatesday)

do i=0 to &Periods;

futdates=intnx('month',input("&Periods",mmddyy10.),i,'end');

futdatesday=day(intnx('month',input("&Periods",mmddyy10.),i,'end'));

call symputx(cats('DtVar',put(i+1,8.)),put(futdates,mmddyy10.));

call symputx(cats('DtVarDay',put(i+1,8.)),put(futdatesday,2.));

format futdates mmddyy10. futdatesday 2.;

output;

end;

run;

then...

newcol1=x-&futdatesday1;

newcol2=x-&futdatesday2;

newcol3=x-&futdatesday3;

newcol4=x-&futdatesday4;

newcol5=x-&futdatesday5;

newcol6=x-&futdatesday6;

newcol7=x-&futdatesday7;

newcol8=x-&futdatesday8;

newcol9=x-&futdatesday9;

newcol10=x-&futdatesday10;

Next run it can be 20. 25. etc. I guess I run into trouble making it dynamic...

Re: Stuck on adding columns with calculated values to table (macro vars)

Linlin — Fri, 26 Jul 2013 14:49:29 GMT

%let y1=1;
%let y2=12;
%let y3=15;
%let n=3;

data old;
input x @@;
cards;
2 3 4
;

%macro test;
data new;
set old;
%do i=1 %to &n;
newcol&i=x-&&y&i;
%end;
run;
%mend;
%test
proc print data=new;run;

Re: Stuck on adding columns with calculated values to table (macro vars)

jbear — Fri, 26 Jul 2013 15:01:46 GMT

Awesome, thanks...I have to dive into macros.