DATA Step, Macro, Functions and more

assign last month value to previous 2 records

Accepted Solution Solved
Reply
Contributor sss
Contributor
Posts: 65
Accepted Solution

assign last month value to previous 2 records

[ Edited ]

HI All,

 

I am currently working on below logic, i tried couple of tricks but didn't got desired results. 

I want to group 3 countinues months and assign last month values for previous 2 months including current month record .

 

for instance: dec16,jan17 and feb17 i want to assign as feb17. Basically wants to tried as quarter(not using qtr function).

i have enclosed the sample code and desired output, your help is appreciable.

 

data have;
input run_month:date9.;
format run_month date9. ;
datalines;
01dec2016
01jan2017
01feb2017
01mar2017
01apr2017
01may2017
01jun2017
01jul2017
01aug2017
01sep2017
01oct2017
01nov2017
01dec2017
;
run;

 

Desired output:

 

Months Qtr_month
1-Dec-16 1-Feb-17
1-Jan-17 1-Feb-17
1-Feb-17 1-Feb-17
1-Mar-17 1-May-17
1-Apr-17 1-May-17
1-May-17 1-May-17
1-Jun-17 1-Aug-17
1-Jul-17 1-Aug-17
1-Aug-17 1-Aug-17
1-Sep-17 1-Nov-17
1-Oct-17 1-Nov-17
1-Nov-17 1-Nov-17
1-Dec-17 1-Feb-17

Accepted Solutions
Solution
‎07-16-2017 02:22 AM
Super User
Posts: 5,080

Re: assign last month value to previous 2 records

I think Shmuel is using the right tools, but the wrong logic.  I would try:

 

data want;

set have;

retain count 3;

count = count - 1;

qtr_month = intnx('month', run_month, count);

if count=0 then count=3;

drop count;

run;

View solution in original post


All Replies
Trusted Advisor
Posts: 1,369

Re: assign last month value to previous 2 records

Assuming data is sorted and months are continues, try next code:

data want;
  set have;
        retain count 0; drop count;
        count+1;
        if count=3 then count=0;
        qtr_month = intnx('month',months,count);
run;
Solution
‎07-16-2017 02:22 AM
Super User
Posts: 5,080

Re: assign last month value to previous 2 records

I think Shmuel is using the right tools, but the wrong logic.  I would try:

 

data want;

set have;

retain count 3;

count = count - 1;

qtr_month = intnx('month', run_month, count);

if count=0 then count=3;

drop count;

run;

Contributor sss
Contributor
Posts: 65

Re: assign last month value to previous 2 records

Yes its works, thank you shmuel and Astounding Smiley Happy

 

Super User
Posts: 17,784

Re: assign last month value to previous 2 records

Note that you can probably do this directly with INTNX using some of the shift options. But, here's one way using nested INTNX, there's probably a simpler way, but this doesn't depend on the order of the data and can be used anywhere in SAS.

 

Also, your last entry in your WANT data set is incorrect, it should be 2018, not 2017. 

 

data want;
set have;
want = intnx('month', 
                     intnx('qtr', 
                                 intnx('month', run_month, 1, 'b'), 
                                 0, 'e'), 
                                         -1, 'b');
format want date9.;
run;
☑ This topic is SOLVED.

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

Discussion stats
  • 4 replies
  • 176 views
  • 2 likes
  • 4 in conversation