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

Hi all

 

So I think I need to use a loop to create what I want here.

I have listed what I start with, so I have a date, month number, value and type.

 

The type private has 4 months but the month number for the last value is 5 as there is a 5 month gap to get this value, so no month 4 value exists.

 

What I want to do is loop through this data and say when the gap between month number is more than 1, insert a row for this number e.g mont number 4 and use the vlaue for the prior month, e.g value for month 3.

 

Is this possible?

 

There could be an instance where the gap is 2 months, 3 month etc so some sort of limit would need to be applied if possible. 

 

Thanks,

Aidan

 

Starting Data 
DATEVALUEMONTH_NUMBERTYPE
01-Jan-11€88,960.501Private                  
01-Jan-11€602,170.202Private                  
01-Jan-11€861,442.853Private                  
01-Jan-11€876,937.625Private                  
01-Jan-11€720.001Public                   
01-Jan-11€217,515.002Public                   
01-Jan-11€552,048.004Public                   
01-Jan-11€866,307.975Public                   
01-Jan-11€1,221,582.266Public                   
01-Jan-11€831,182.837Public                   
    
    
What I want to achieve 
DATEVALUEMONTH_NUMBERTYPE
01-Jan-11€88,960.501Private                  
01-Jan-11€602,170.202Private                  
01-Jan-11€861,442.853Private                  
01-Jan-11€861,442.854Private                  
01-Jan-11€876,937.625Private                  
01-Jan-11€720.001Public                   
01-Jan-11€217,515.002Public                   
01-Jan-11€217,515.003Public                   
01-Jan-11€552,048.004Public                   
01-Jan-11€866,307.975Public                   
01-Jan-11€1,221,582.266Public                   
01-Jan-11€831,182.837Public                   
1 ACCEPTED SOLUTION

Accepted Solutions
Astounding
PROC Star

A DATA step should be able to handle this:

 

data want;

set have end=done;

output;

if done=0;

nextobs = _n_ + 1;

set have (keep=month rename=(month=next_month)) point=nextobs;

if next_month > month + 1 then do month=month+1 to next_month-1;

   output;

end;

drop nextobs;

run;

 

It's untested but looks like it should work.

View solution in original post

4 REPLIES 4
Astounding
PROC Star

A DATA step should be able to handle this:

 

data want;

set have end=done;

output;

if done=0;

nextobs = _n_ + 1;

set have (keep=month rename=(month=next_month)) point=nextobs;

if next_month > month + 1 then do month=month+1 to next_month-1;

   output;

end;

drop nextobs;

run;

 

It's untested but looks like it should work.

Aidan
Quartz | Level 8

Thanks for your reply, I have applied the code below into a data step, the output columns are as follows but nothing is returning, have you any recommendations? 

 

TYPE
DATE 
VALUE
MONTH_NUMBER
NEXT_MONTH

 

proc sort data =&_input1
out=temp1;
by TYPE DATE MONTH_NUMBER;
run;

data &_output1;
set temp1;
by TYPE DATE MONTH_NUMBER;
end=done;
output;
if done=0;
nextobs = _n_ + 1;
set temp1 (keep=DATE rename=(DATE =next_month)) point=nextobs;
if next_month > DATE + 1 then do DATE =DATE +1 to next_month-1;
output;
end;
drop nextobs;
run;

Astounding
PROC Star

end=done should not be a separate statement.  It should be an option on the SET statement.

Aidan
Quartz | Level 8
Excellent, makes sense and works very well, thank you very much

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
  • 707 views
  • 2 likes
  • 2 in conversation