Solved
Contributor
Posts: 64

# Expand on Loop query

 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.4 High-Tech Facility 01-Feb-11 3 44531.5 High-Tech Facility 01-Feb-11 4 70708.8 High-Tech Facility 01-Feb-11 5 87050 High-Tech Facility 01-Feb-11 6 56310.8 High-Tech Facility 01-Feb-11 7 152429 High-Tech Facility 01-Feb-11 8 90115.4 High-Tech Facility 01-Feb-11 9 80930.1 High-Tech Facility 01-Feb-11 10 79364.5 High-Tech Facility 01-Feb-11 11 175093 High-Tech Facility 01-Feb-11 12 101078 High-Tech Facility 01-Feb-11 13 91643.8 High-Tech Facility 01-Feb-11 14 64509.8 High-Tech Facility 01-Feb-11 15 15164.8 High-Tech Facility 01-Feb-11 16 98763.4 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

Accepted Solutions
Solution
‎07-13-2016 06:12 AM
Contributor
Posts: 64

## Re: Expand on Loop query

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.

All Replies
Contributor
Posts: 25

## Re: Expand on Loop query

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;```
Contributor
Posts: 64

## Re: Expand on Loop query

If I wanted to use a variable instead of 60 e.g variable name called FACTOR_MONTH it doesn't seem to work when I replace the 60 with the variable, any ideas?

Thank you for the help
Contributor
Posts: 25

## Re: Expand on Loop query

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.

Super User
Posts: 6,785

## Re: Expand on Loop query

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.

Solution
‎07-13-2016 06:12 AM
Contributor
Posts: 64

## Re: Expand on Loop query

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.