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

[jbear]

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

[Reeza]

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.

[jbear]

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

[Linlin]

%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;

[jbear]

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...

[Linlin · Accepted solution]

%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;

[jbear]

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