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

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
1 ACCEPTED SOLUTION

Accepted Solutions
Astounding
PROC Star

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

4 REPLIES 4
Shmuel
Garnet | Level 18

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;
Astounding
PROC Star

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;

sss
Fluorite | Level 6 sss
Fluorite | Level 6

Yes its works, thank you shmuel and Astounding 🙂

 

Reeza
Super User

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;

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
  • 4 replies
  • 1051 views
  • 2 likes
  • 4 in conversation