BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Crubal
Quartz | Level 8

I have a data set (Table A) like below:

 

Location_id |  Arrival_Date | Length_of_Stay | Demand   | 

------------+---------------+--------------- +----------+

   L_1      | 23-JUL-16     |  1             | 5        |

   L_1      | 23-JUL-16     |  2             | 7        |

   L_1      | 23-JUL-16     |  3             | 8        |

   L_1      | 23-JUL-16     |  4             | 3        |

   L_1      | 24-JUL-16     |  1             | 3        |

   L_1      | 24-JUL-16     |  2             | 2        |

   L_1      | 24-JUL-16     |  3             | 4        |

   L_1      | 25-JUL-16     |  1             | 5        | 

...........  ............    .............   .......

I would like to transfer to the following table:

Location_id |  Stay_Date |  Demand   | 

 L_1        | 23-JUL-16  |  23       |

 L_1        | 24-JUL-16  |  27       |

 L_1        | 25-JUL-16  |  22       |

 L_1        | 26-JUL-16  |  7        |

Where demand is measured as Stay_Date, which is calculated according to Arrival_Date and Length_of_Stay.

How can I make it in SAS? Thanks!

 
 

 

1 ACCEPTED SOLUTION

Accepted Solutions
PGStats
Opal | Level 21

One simple way to do this then is to expand the data to daily demand:

 


data temp;
set have;
do i = 1 to Length_of_stay;
    Stay_date = intnx("day", Arrival_date, i-1);
    output;
    end;
format stay_date yymmdd10.;
drop i;
run;

proc sql;
create table want as
select location_id, Stay_date, sum(demand) as Demand
from temp
group by location_id, stay_date;
select * from want;
quit;
PG

View solution in original post

5 REPLIES 5
Patrick
Opal | Level 21

There are many ways to get what you want like:

1. Proc Means

2. Proc SQL with a group by statement

3. A SAS data step with a Retain for the sum, "by location_id arrival_date" and output "if last.arrival_date"

4. ....

PGStats
Opal | Level 21

Looks like you could do:

 

proc sql;
create table B as
select 
	location_id, 
	intnx("DAY", Arrival_date, Length_of_Stay-1) as Stay_date format=yymmdd10.,
	sum(Demand) as Demand
from A
group by location_id, calculated Stay_date;
quit;
PG
Crubal
Quartz | Level 8

Hi PG,

 

I used your code and generate the result where demand is measure by (Arrival_Date + Length_of_Stay -1) 

 

I apologize that I did not explain clearly. 

 

For example, date 23-Jul-16, demand is calculated by adding the first 4 rows. Since the first row spend 1 day, the second row spend 2 days, the third spend 3 days, and the fourth spend 4 days; and they are all staying at 23-Jul-16. And date 24-Jul-16 is calculated by adding rows 2,3,4 and rows 5,6,7 since they all stay at 24-Jul-16. Thanks!

PGStats
Opal | Level 21

One simple way to do this then is to expand the data to daily demand:

 


data temp;
set have;
do i = 1 to Length_of_stay;
    Stay_date = intnx("day", Arrival_date, i-1);
    output;
    end;
format stay_date yymmdd10.;
drop i;
run;

proc sql;
create table want as
select location_id, Stay_date, sum(demand) as Demand
from temp
group by location_id, stay_date;
select * from want;
quit;
PG
Crubal
Quartz | Level 8

Thanks PG, the do loop is pretty helpful!

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
  • 923 views
  • 1 like
  • 3 in conversation