topic Re: Loop Query in SAS Programming

Loop Query

Aidan — Tue, 12 Jul 2016 10:36:57 GMT

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

Re: Loop Query

Aidan — Tue, 12 Jul 2016 10:47:59 GMT

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

Re: Loop Query

RW9 — Tue, 12 Jul 2016 10:59:29 GMT

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;

Re: Loop Query

Aidan — Tue, 12 Jul 2016 11:04:57 GMT

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 &_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

Re: Loop Query

RW9 — Tue, 12 Jul 2016 11:18:26 GMT

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;

Re: Loop Query

Aidan — Tue, 12 Jul 2016 11:21:59 GMT

I am quiet new to SAS code, can you elaborate more please and to how I implement this with current code?

Would I have this after the first block of code?