Hi all
I have the following code which produces the data as follows below.
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;
output;
end;
drop nextobs;
run;
data &_output1;
set temp3 ;
run;
FACTOR | DATE | MONTH | AMOUNT | MAX |
tech | 01-Feb-11 | 1 | 25064.3 | 64 |
tech | 01-Feb-11 | 2 | 77980.36 | 64 |
tech | 01-Feb-11 | 3 | 44531.48 | 64 |
tech | 01-Feb-11 | 4 | 70708.78 | 64 |
tech | 01-Feb-11 | 5 | 87050.04 | 64 |
tech | 01-Feb-11 | 6 | 56310.83 | 64 |
tech | 01-Feb-11 | 7 | 152429.4 | 64 |
tech | 01-Feb-11 | 8 | 90115.41 | 64 |
tech | 01-Feb-11 | 9 | 80930.07 | 64 |
tech | 01-Feb-11 | 10 | 79364.52 | 64 |
tech | 01-Feb-11 | 11 | 175092.7 | 64 |
tech | 01-Feb-11 | 12 | 101078 | 64 |
tech | 01-Feb-11 | 13 | 91643.83 | 64 |
tech | 01-Feb-11 | 14 | 64509.84 | 64 |
tech | 01-Feb-11 | 15 | 15164.82 | 64 |
tech | 01-Feb-11 | 16 | 98763.39 | 64 |
tech | 01-Feb-11 | 17 | 1359.03 | 64 |
tech | 01-Feb-11 | 18 | 0 | 64 |
tech | 01-Feb-11 | 19 | 1675 | 64 |
tech | 01-Feb-11 | 20 | 891.72 | 64 |
tech | 01-Feb-11 | 21 | 1045 | 64 |
tech | 01-Feb-11 | 22 | 0 | 64 |
tech | 01-Feb-11 | 23 | 0 | 64 |
tech | 01-Feb-11 | 24 | 325.79 | 64 |
However what I want the loop to do is keep running up to month 64 as this is the max month for this date, so I need to create data for another 40 rows.
If possible on top of this I want the AMOUNT to be set to 0 each time a row is created.
Kind Regards
Aidan
My Input is as follows in case this comes in use
FACTOR | DATE | MONTH | AMOUNT |
tech | ######## | 1 | 25064.3 |
tech | ######## | 2 | 77980.36 |
tech | ######## | 3 | 44531.48 |
tech | ######## | 4 | 70708.78 |
tech | ######## | 5 | 87050.04 |
tech | ######## | 6 | 56310.83 |
tech | ######## | 7 | 152429.4 |
tech | ######## | 8 | 90115.41 |
tech | ######## | 9 | 80930.07 |
tech | ######## | 10 | 79364.52 |
tech | ######## | 11 | 175092.7 |
tech | ######## | 12 | 101078 |
tech | ######## | 13 | 91643.83 |
tech | ######## | 14 | 64509.84 |
tech | ######## | 15 | 15164.82 |
tech | ######## | 16 | 98763.39 |
tech | ######## | 17 | 1359.03 |
tech | ######## | 19 | 1675 |
tech | ######## | 20 | 891.72 |
tech | ######## | 21 | 1045 |
tech | ######## | 24 | 325.79 |
Sorry, your problem is not clear to me. What I got from that was you want 64 observations per one in the input dataset, so:
data want; set have; do i=0 to 63; month=month+i; output; end; run;
Or maybe its because your missing some months in the original data, then simply merging the complete set back to the original should suffice:
data temp; do month=1 to 64; output; end; run; proc sql; create table WANT as select A.FACTOR, A.DATE, COALESCE(A.MONTH,B.MONTH) as MONTH, A.AMOUNT from HAVE A full join TEMP B on A.MONTH=B.MONTH; quit;
I apologize, so my initial data set is below
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 |
My original problem was that 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;
output;
end;
drop nextobs;
run;
data &_output1;
set temp3 ;
run;
Now my issue is that if I had a my month number for 1/feb/2011 how would I loop up to this month number, e.g month 64.
Note the max month will vary so for 1/feb/2011 is could be 64 but for 01/mar/2011 it could be 61
Thanks
Aidan
You can put your variable in the do loop, and any further processing before the output:
data want; set have; do i=0 to month; date=intnx('month',date,1); /* any other processing here */ output; end; run;
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.