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 |
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.
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.
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;
end=done should not be a separate statement. It should be an option on the SET statement.
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!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.