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

Hi all,

I am following up on my previous question on creating macro variables for the change variables (last_3month, prev_3month, last_6month, prev_6month, last_12month, prev_12month). Below is a summary of what I wanted to do before:

Given a macro variable &latest_date in the format MONYY (e.g. SEP14), I would like a way to make 6 macro variables:

Last_3month  JUL14-SEP14

Prev_3month  APR14-JUN14

Last_6month  APR14-SEP14

Prev_6month  OCT14-MAR14

Last_12month OCT13-SEP14

Prev_12month OCT12-SEP13

Now I have something a bit challenging. Instead of having a fixed &latest_date for each observation, now I have a new date variable called EVAL_BEFORE_DATE-ddmmmyyyy11. format (along with other numeric variables JAN14--DEC14,JAN13-DEC13), based on which I would like create these change variables for each observation. For example,

For the first observation,

if month(01-01-2013)=JAN, then

Last_3month  NOV12-JAN13

Prev_3month  AUG12-OCT12

Last_6month  AUG12-JAN13

Prev_6month  FEB11-JUL12

Last_12month FEB12-JAN13

Prev_12month FEB11-JAN12

For the second observation,

if month(01-09-2014)=SEP, then

Last_3month  JUL14-SEP14

Prev_3month  APR14-JUN14

Last_6month  APR14-SEP14

Prev_6month  OCT14-MAR14

Last_12month OCT13-SEP14

Prev_12month OCT12-SEP13

Automating the process of creating those change variables would be the same as before, but I am a bit confused how to automate the getting the month part. Any suggestions would be greatly appreciated. Thanks very much.

1 ACCEPTED SOLUTION

Accepted Solutions
Reeza
Super User

Here's an example that uses VvalueX and the INTNX function to generate the months. You can set up separate arrays for each lag period and recalculated as needed.

*Generate sample data;

data have;

array Y2(12) Jan14 Feb14 Mar14 Apr14 May14 Jun14 Jul14 Aug14 Sep14 Oct14 Nov14 Dec14;

format last_date date9.;

do j=1 to 2;

do i=1 to 12;

y2(i)=rand('normal', 52, 5);

end;

if j=1 then last_date='01May2014'd;

else last_date='01Sep2014'd;

output;

end;

drop j i;

run;

*Sample output for previous 3 months;

data want;

set have;

array v_3(3) v1 v2 v3;

do i=0 to 2;

var=put(intnx('month', last_date, -1*i), monyy5.);

v_3(i+1)=vvaluex(var);

end;

month3_mean=mean(of v_3(*));

month3_std=std(of v_3(*));

run;

View solution in original post

10 REPLIES 10
Reeza
Super User

Depending on what you're doing in your future process I'm going to generally recommend against this method. Macro variables are good but this might be over usage.

Have you considered an array with the vname and/or vnext functions?

Dil
Calcite | Level 5 Dil
Calcite | Level 5

Thanks for your reply Reeza. I see. An array might a good option too. But do you mean creating an array of SAS variables (JAN14--DEC14) or the change variables (e.g., last previous months) ? Can you please provide a bit more hints on using arrays? Thanks,

Reeza
Super User

can you provide sample data and output? I have no idea of what you're using it for so can only provide vague suggestions.

Dil
Calcite | Level 5 Dil
Calcite | Level 5

Hi Reeza,

Below are the sample data and output.

sample data:

id JAN11--DEC11  JAN12--DEC12  JAN13 --DEC13 JAN14--DEC14  EVAL_BEFORE_DATE

1    23       45         34         23         41         27       51         65         01-01-2013

2    24       43         21         32          34         56       76        21         01-09-2014


Sample output

id JAN11--DEC11  JAN12--DEC12  JAN13 --DEC13 JAN14--DEC14  EVAL_BEFORE_DATE    last_3month        prev_3month           last_6moth                prev_6month 

1    23       45         34         23         41         27       51         65         01-01-2013               NOV12-JAN13       AUG12-OCT12         AUG12-JAN13          FEB11-JUL12

2    24       43         21         32          34         56       76        21         01-09-2014               JUL14-SEP14        APR14-JUN14          APR14-SEP14          OCT14-MAR14


last_12month       prev_12month

FEB12-JAN13        FEB11-JAN12

OCT13-SEP14       OCT12-SEP13

where last_3month--prev_12month are obtained based on the EVAL_BEFORE_DATE. For the sake of clarification, I put those change variables last_3month--prev_12month in the output data. But I would do some summary statistics like calculating the mean on those change variables instead. Thank you.

Reeza
Super User

What are you using the last_12month etc variables for? My theory is that you don't actually need them.

Dil
Calcite | Level 5 Dil
Calcite | Level 5

Hi Reeza,

I want to obtain some summary statistics on those change variables (e.g.,last_12month). So those added columns on the output dataset should be those summary statistics (mean or standard deviations). I put those values (e.g.,NOV12-JAN13) just for the sake of illustration.

Reeza
Super User

Change your variable name structure to V201201 -- V201212 then you can use the method suggested here:

SAS Sum specific column that the name are stored in other column - Stack Overflow

The issue/annoyance to me would be the manual standard deviation calculation. 

I'd consider flipping the data and then using proc means instead myself.

Reeza
Super User

Here's an example that uses VvalueX and the INTNX function to generate the months. You can set up separate arrays for each lag period and recalculated as needed.

*Generate sample data;

data have;

array Y2(12) Jan14 Feb14 Mar14 Apr14 May14 Jun14 Jul14 Aug14 Sep14 Oct14 Nov14 Dec14;

format last_date date9.;

do j=1 to 2;

do i=1 to 12;

y2(i)=rand('normal', 52, 5);

end;

if j=1 then last_date='01May2014'd;

else last_date='01Sep2014'd;

output;

end;

drop j i;

run;

*Sample output for previous 3 months;

data want;

set have;

array v_3(3) v1 v2 v3;

do i=0 to 2;

var=put(intnx('month', last_date, -1*i), monyy5.);

v_3(i+1)=vvaluex(var);

end;

month3_mean=mean(of v_3(*));

month3_std=std(of v_3(*));

run;

RW9
Diamond | Level 26 RW9
Diamond | Level 26

As an alternative, try normalizing your data.  Merely changing the structure from wide to long sometimes helps greatly. 

ID          DATE               VAL

1            01JAN11          23

1            01DEC11         45

1            01JAN12          34

...

Then its a simple case of using some SQL queries to roll the data up.

Dil
Calcite | Level 5 Dil
Calcite | Level 5

Hi Reeza,

Thanks a lot for pointing out this method. It worked out for me. Below are the code I extended for my purposes.

*Sample output for change variables months;

data wantFinal (drop=v:);

set have;

/*last_3month*/

array l_3(3) v1 v2 v3;

do i=-3, -2, -1;

var=put(intnx('month', last_date, i), monyy5.);

l_3(i+4)=vvaluex(var);

end;

month3l_mean=mean(of l_3(*));

month3l_std=std(of l_3(*));

*previous 3 month;

array p_3(3) v4 v5 v6;

do i=-6,-5,-4;

var=put(intnx('month', last_date, i), monyy5.);

p_3(i+7)=vvaluex(var);

end;

month3p_mean=mean(of p_3(*));

month3p_std=std(of p_3(*));

*last 6 month;

array l_6(6) v61 v62 v63 v64 v65 v66;

do i=-6,-5,-4,-3,-2,-1;

var=put(intnx('month', last_date, i), monyy5.);

l_6(i+7)=vvaluex(var);

end;

month6l_mean=mean(of l_6(*));

month6l_std=std(of l_6(*));

*previous 6 month;

array p_6(6) v67 v68 v69 v610 v611 v612;

do i=-12,-11,-10,-9,-8,-7;

var=put(intnx('month', last_date, i), monyy5.);

p_6(i+13)=vvaluex(var);

end;

month6p_mean=mean(of p_6(*));

month6p_std=std(of p_6(*));

*last 12 month;

array l_12(12) v121 v122 v123 v124 v125 v126 v127 v128 v129 v1210 v1211 v1212;

do i=-12,-11,-10,-9,-8,-7,-6,-5,-4,-3,-2,-1;

var=put(intnx('month', last_date, i), monyy5.);

l_12(i+13)=vvaluex(var);

end;

month12l_mean=mean(of l_12(*));

month12l_std=std(of l_12(*));

*previous 12 month;

array p_12(12) v1210 v1220 v1230 v1240 v1250 v1260 v1270 v1280 v1290 v12100 v12110 v12120;

do i=-24,-23,-22,-21,-20,-19,-18,-17,-16,-15,-14,-13;

var=put(intnx('month', last_date, i), monyy5.);

p_12(i+25)=vvaluex(var);

end;

month12p_mean=mean(of p_12(*));

month12p_std=std(of p_12(*));

run;

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!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

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
  • 10 replies
  • 1550 views
  • 4 likes
  • 3 in conversation