BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Aidan
Quartz | Level 8
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

1 ACCEPTED SOLUTION

Accepted Solutions
Aidan
Quartz | Level 8

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

5 REPLIES 5
sh0e
Obsidian | Level 7

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;
Aidan
Quartz | Level 8
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
sh0e
Obsidian | Level 7

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. 

Astounding
PROC Star

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.

Aidan
Quartz | Level 8

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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