FACTOR | DATE | MONTH | AMOUNT |
TECH | 01-Feb-11 | 1 | 25064.3 |
TECH | 01-Feb-11 | 2 | 77980.36 |
TECH | 01-Feb-11 | 3 | 44531.48 |
TECH | 01-Feb-11 | 4 | 70708.78 |
TECH | 01-Feb-11 | 5 | 87050.04 |
TECH | 01-Feb-11 | 6 | 56310.83 |
TECH | 01-Feb-11 | 7 | 152429.35 |
TECH | 01-Feb-11 | 8 | 90115.41 |
TECH | 01-Feb-11 | 9 | 80930.07 |
TECH | 01-Feb-11 | 10 | 79364.52 |
TECH | 01-Feb-11 | 11 | 175092.67 |
TECH | 01-Feb-11 | 12 | 101078.03 |
TECH | 01-Feb-11 | 13 | 91643.83 |
TECH | 01-Feb-11 | 14 | 64509.84 |
TECH | 01-Feb-11 | 15 | 15164.82 |
TECH | 01-Feb-11 | 16 | 98763.39 |
TECH | 01-Feb-11 | 17 | 1359.03 |
TECH | 01-Feb-11 | 19 | 1675 |
TECH | 01-Feb-11 | 20 | 891.72 |
TECH | 01-Feb-11 | 21 | 1045 |
TECH | 01-Feb-11 | 24 | 325.79 |
There was a gap between 21 and 24 month.
I wanted to solve this by using the code below which populated the month 22 and 23 with data.
proc sort data =&_input1
out=temp1;
by FACTOR DATE MONTH;
run;
data temp3;
set temp1 end=done;
by FACTOR DATE MONTH;
output;
if done=0;
nextobs = _n_ + 1;
set temp1 (keep=MONTHrename=(MONTH=next_month)) point=nextobs;
if next_month > MONTH+ 1 then do MONTH=MONTH+1 to next_month-1;
AMOUNT=0;
output;
end;
drop nextobs;
run;
data &_output1;
set temp3 ;
run;
Output is follows
High-Tech Facility | 01-Feb-11 | 1 | 25064.3 |
High-Tech Facility | 01-Feb-11 | 2 | 77980.36 |
High-Tech Facility | 01-Feb-11 | 3 | 44531.48 |
High-Tech Facility | 01-Feb-11 | 4 | 70708.78 |
High-Tech Facility | 01-Feb-11 | 5 | 87050.04 |
High-Tech Facility | 01-Feb-11 | 6 | 56310.83 |
High-Tech Facility | 01-Feb-11 | 7 | 152429.4 |
High-Tech Facility | 01-Feb-11 | 8 | 90115.41 |
High-Tech Facility | 01-Feb-11 | 9 | 80930.07 |
High-Tech Facility | 01-Feb-11 | 10 | 79364.52 |
High-Tech Facility | 01-Feb-11 | 11 | 175092.7 |
High-Tech Facility | 01-Feb-11 | 12 | 101078 |
High-Tech Facility | 01-Feb-11 | 13 | 91643.83 |
High-Tech Facility | 01-Feb-11 | 14 | 64509.84 |
High-Tech Facility | 01-Feb-11 | 15 | 15164.82 |
High-Tech Facility | 01-Feb-11 | 16 | 98763.39 |
High-Tech Facility | 01-Feb-11 | 17 | 1359.03 |
High-Tech Facility | 01-Feb-11 | 18 | 0 |
High-Tech Facility | 01-Feb-11 | 19 | 1675 |
High-Tech Facility | 01-Feb-11 | 20 | 891.72 |
High-Tech Facility | 01-Feb-11 | 21 | 1045 |
High-Tech Facility | 01-Feb-11 | 22 | 0 |
High-Tech Facility | 01-Feb-11 | 23 | 0 |
High-Tech Facility | 01-Feb-11 | 24 | 325.79 |
However, If I wanted to expand on this, lets say the max month is 60 (which it won't always be)
So currently the data runs up to month 24 but if I wanted to create blank data for each month up to month 60, can I amend the existing loop to achieve this?
e.g. data to create
date month amount
1/feb/11 25 0
1/feb/11 26 0
1/feb/11 27 0
......
Thanks
Aidan
Hi all
I have managed to solve this myself, but thank you kindly for your help.
I created a blank data set which looped and created a month from 0 to the maximum month for each date.
Thanks all for your help
You can use an SQL join to make the data set have proper rank. For example,
data seed; date = '11FEB2015'D; do month = 1 to 60; output;
end;
run;
proc sql;
create table full as
select *
from seed left join raw
on raw.date = seed.date & raw.month = seed.month;
quit;
run;
data full;
set full;
if amount = . then amount = 0;
run;
Puzzling. The value of 60 is external in my example. You can't use FACTOR_MONTH in the SEED data set because that column is not available. You could use a macro symbol to hold the value of 60. I guess I really don't understand your question.
You have this line in the current program:
if done=0;
That line could be removed, and replaced with:
if done then do;
amount=0;
if month < 60 then do month = month + 1 to 60;
output;
end;
stop;
end;
The order is important. This code does not belong at the bottom of the DATA step. It needs to replace that one line in the middle of the DATA step.
Hi all
I have managed to solve this myself, but thank you kindly for your help.
I created a blank data set which looped and created a month from 0 to the maximum month for each date.
Thanks all for your help
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.