## How to combine rows into one

Solved
Regular Contributor
Posts: 154

# 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
Posts: 5,541

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

All Replies
Posts: 4,741

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

Posts: 5,541

## 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
Regular Contributor
Posts: 154

## 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
Posts: 5,541

## 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
Regular Contributor
Posts: 154

## Re: How to combine rows into one

Thanks PG, the do loop is pretty helpful!

☑ This topic is solved.