Loop Query

Accepted Solution Solved
Reply
Contributor
Posts: 57
Accepted Solution

Loop Query

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                   

Accepted Solutions
Solution
‎07-08-2016 09:50 AM
Respected Advisor
Posts: 5,005

Re: Loop Query

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


All Replies
Solution
‎07-08-2016 09:50 AM
Respected Advisor
Posts: 5,005

Re: Loop Query

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.

Contributor
Posts: 57

Re: Loop Query

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;

Respected Advisor
Posts: 5,005

Re: Loop Query

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

Contributor
Posts: 57

Re: Loop Query

Excellent, makes sense and works very well, thank you very much
☑ This topic is SOLVED.

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

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