DATA Step, Macro, Functions and more

How to combine rows into one

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 107
Accepted Solution

How to combine rows into one

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!

 
 

 


Accepted Solutions
Solution
‎10-18-2016 09:28 AM
Respected Advisor
Posts: 4,640

Re: How to combine rows into one

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


All Replies
Respected Advisor
Posts: 3,887

Re: How to combine rows into one

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. ....

Respected Advisor
Posts: 4,640

Re: How to combine rows into one

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
Frequent Contributor
Posts: 107

Re: How to combine rows into one

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!

Solution
‎10-18-2016 09:28 AM
Respected Advisor
Posts: 4,640

Re: How to combine rows into one

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
Frequent Contributor
Posts: 107

Re: How to combine rows into one

Thanks PG, the do loop is pretty helpful!

☑ This topic is SOLVED.

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

Discussion stats
  • 5 replies
  • 363 views
  • 1 like
  • 3 in conversation