Solved
Contributor
Posts: 29

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

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

Accepted Solutions
Solution
‎07-26-2013 10:49 AM
Super Contributor
Posts: 1,636

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

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

All Replies
Super User
Posts: 23,677

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

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.

Contributor
Posts: 29

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

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

Super Contributor
Posts: 1,636

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

Contributor
Posts: 29

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

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

Solution
‎07-26-2013 10:49 AM
Super Contributor
Posts: 1,636

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

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

Contributor
Posts: 29

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

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

🔒 This topic is solved and locked.