BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
jbear
Calcite | Level 5

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

1 ACCEPTED SOLUTION

Accepted Solutions
Linlin
Lapis Lazuli | Level 10

%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

6 REPLIES 6
Reeza
Super User

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
Calcite | Level 5

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
Lapis Lazuli | Level 10

%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
Calcite | Level 5

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
Lapis Lazuli | Level 10

%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
Calcite | Level 5

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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