topic Re: assign last month value to previous 2 records in SAS Programming

assign last month value to previous 2 records

sss — Sun, 16 Jul 2017 04:08:00 GMT

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

Re: assign last month value to previous 2 records

Shmuel — Sun, 16 Jul 2017 04:25:46 GMT

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;

Re: assign last month value to previous 2 records

Astounding — Sun, 16 Jul 2017 05:53:41 GMT

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;

Re: assign last month value to previous 2 records

sss — Sun, 16 Jul 2017 06:21:37 GMT

Yes its works, thank you shmuel and Astounding 🙂

Re: assign last month value to previous 2 records

Reeza — Sun, 16 Jul 2017 06:56:27 GMT

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;