DATA Step, Macro, Functions and more

Expand on Loop query

Accepted Solution Solved
Reply
Contributor
Posts: 64
Accepted Solution

Expand on Loop query

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

 

 

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-11125064.3
High-Tech Facility       01-Feb-11277980.36
High-Tech Facility       01-Feb-11344531.48
High-Tech Facility       01-Feb-11470708.78
High-Tech Facility       01-Feb-11587050.04
High-Tech Facility       01-Feb-11656310.83
High-Tech Facility       01-Feb-117152429.4
High-Tech Facility       01-Feb-11890115.41
High-Tech Facility       01-Feb-11980930.07
High-Tech Facility       01-Feb-111079364.52
High-Tech Facility       01-Feb-1111175092.7
High-Tech Facility       01-Feb-1112101078
High-Tech Facility       01-Feb-111391643.83
High-Tech Facility       01-Feb-111464509.84
High-Tech Facility       01-Feb-111515164.82
High-Tech Facility       01-Feb-111698763.39
High-Tech Facility       01-Feb-11171359.03
High-Tech Facility       01-Feb-11180
High-Tech Facility       01-Feb-11191675
High-Tech Facility       01-Feb-1120891.72
High-Tech Facility       01-Feb-11211045
High-Tech Facility       01-Feb-11220
High-Tech Facility       01-Feb-11230
High-Tech Facility       01-Feb-1124325.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

Posted in reply to Astounding

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

View solution in original post


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: 5,513

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

Posted in reply to Astounding

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

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 235 views
  • 0 likes
  • 3 in conversation