Solved
Contributor
Posts: 64

# 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 DATE VALUE MONTH_NUMBER TYPE 01-Jan-11 €88,960.50 1 Private 01-Jan-11 €602,170.20 2 Private 01-Jan-11 €861,442.85 3 Private 01-Jan-11 €876,937.62 5 Private 01-Jan-11 €720.00 1 Public 01-Jan-11 €217,515.00 2 Public 01-Jan-11 €552,048.00 4 Public 01-Jan-11 €866,307.97 5 Public 01-Jan-11 €1,221,582.26 6 Public 01-Jan-11 €831,182.83 7 Public What I want to achieve DATE VALUE MONTH_NUMBER TYPE 01-Jan-11 €88,960.50 1 Private 01-Jan-11 €602,170.20 2 Private 01-Jan-11 €861,442.85 3 Private 01-Jan-11 €861,442.85 4 Private 01-Jan-11 €876,937.62 5 Private 01-Jan-11 €720.00 1 Public 01-Jan-11 €217,515.00 2 Public 01-Jan-11 €217,515.00 3 Public 01-Jan-11 €552,048.00 4 Public 01-Jan-11 €866,307.97 5 Public 01-Jan-11 €1,221,582.26 6 Public 01-Jan-11 €831,182.83 7 Public

Accepted Solutions
Solution
‎07-08-2016 09:50 AM
Super User
Posts: 6,785

## 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.

All Replies
Solution
‎07-08-2016 09:50 AM
Super User
Posts: 6,785

## 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: 64

## Re: Loop Query

Posted in reply to Astounding

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;

Super User
Posts: 6,785

## Re: Loop Query

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

Contributor
Posts: 64

## Re: Loop Query

Posted in reply to Astounding
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
• 334 views
• 2 likes
• 2 in conversation