DATA Step, Macro, Functions and more

Loop Query

Reply
Contributor
Posts: 64

Loop Query

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;

 

 

 

 

FACTORDATEMONTHAMOUNTMAX
tech01-Feb-11125064.364
tech01-Feb-11277980.3664
tech01-Feb-11344531.4864
tech01-Feb-11470708.7864
tech01-Feb-11587050.0464
tech01-Feb-11656310.8364
tech01-Feb-117152429.464
tech01-Feb-11890115.4164
tech01-Feb-11980930.0764
tech01-Feb-111079364.5264
tech01-Feb-1111175092.764
tech01-Feb-111210107864
tech01-Feb-111391643.8364
tech01-Feb-111464509.8464
tech01-Feb-111515164.8264
tech01-Feb-111698763.3964
tech01-Feb-11171359.0364
tech01-Feb-1118064
tech01-Feb-1119167564
tech01-Feb-1120891.7264
tech01-Feb-1121104564
tech01-Feb-1122064
tech01-Feb-1123064
tech01-Feb-1124325.7964

 

 

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 

Contributor
Posts: 64

Re: Loop Query

My Input is as follows in case this comes in use

 

FACTORDATEMONTHAMOUNT
tech########125064.3
tech########277980.36
tech########344531.48
tech########470708.78
tech########587050.04
tech########656310.83
tech########7152429.4
tech########890115.41
tech########980930.07
tech########1079364.52
tech########11175092.7
tech########12101078
tech########1391643.83
tech########1464509.84
tech########1515164.82
tech########1698763.39
tech########171359.03
tech########191675
tech########20891.72
tech########211045
tech########24325.79
Super User
Super User
Posts: 7,997

Re: Loop Query

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

Re: Loop Query

I apologize, so my initial data set is below

FACTORDATEMONTHAMOUNT
TECH01-Feb-11125064.3
TECH01-Feb-11277980.36
TECH01-Feb-11344531.48
TECH01-Feb-11470708.78
TECH01-Feb-11587050.04
TECH01-Feb-11656310.83
TECH01-Feb-117152429.35
TECH01-Feb-11890115.41
TECH01-Feb-11980930.07
TECH01-Feb-111079364.52
TECH01-Feb-1111175092.67
TECH01-Feb-1112101078.03
TECH01-Feb-111391643.83
TECH01-Feb-111464509.84
TECH01-Feb-111515164.82
TECH01-Feb-111698763.39
TECH01-Feb-11171359.03
TECH01-Feb-11191675
TECH01-Feb-1120891.72
TECH01-Feb-11211045
TECH01-Feb-1124325.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 

Super User
Super User
Posts: 7,997

Re: Loop Query

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

Re: Loop Query

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?
Ask a Question
Discussion stats
  • 5 replies
  • 304 views
  • 0 likes
  • 2 in conversation