Loop Query

[Aidan]

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

[Astounding · Accepted solution]

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]

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]

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

[Aidan]

Excellent, makes sense and works very well, thank you very much