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

Accepted Solution Solved
Reply
Contributor
Posts: 29
Accepted Solution

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):

ContrtNameEffective DtEffectiveDtDayPaid AmtFutDateDayPct1FutDateDayPct2FutDateDayPct10
0000001Joe 10/5/20115101-(5/31)1-(5/31)1-(5/30)
0000002Mary11/8/20108121-(8/31)1-(8/31)1-(8/30)

Macro variable values:

futdates17/31/2013futdatesday131
futdates28/31/2013futdatesday231
futdates104/30/2014futdatesday1030

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;

View solution in original post


All Replies
Super User
Posts: 17,836

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

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

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

Need further help from the community? Please ask a new question.

Discussion stats
  • 6 replies
  • 264 views
  • 1 like
  • 3 in conversation