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.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.