topic Re: Expand on Loop query in SAS Programming

Expand on Loop query

Aidan — Tue, 12 Jul 2016 14:03:34 GMT

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

Re: Expand on Loop query

sh0e — Tue, 12 Jul 2016 14:35:03 GMT

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;

Re: Expand on Loop query

Aidan — Tue, 12 Jul 2016 14:55:04 GMT

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

Re: Expand on Loop query

sh0e — Tue, 12 Jul 2016 14:59:11 GMT

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.

Re: Expand on Loop query

Astounding — Tue, 12 Jul 2016 15:25:54 GMT

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.

Re: Expand on Loop query

Aidan — Wed, 13 Jul 2016 10:12:14 GMT

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