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 |
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;
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;
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;
Yes its works, thank you shmuel and Astounding 🙂
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;
Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.
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.
Ready to level-up your skills? Choose your own adventure.