topic Re: How to combine rows into one in SAS Programming

How to combine rows into one

Crubal — Sun, 16 Oct 2016 01:30:19 GMT

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!

Re: How to combine rows into one

Patrick — Sun, 16 Oct 2016 02:02:12 GMT

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

Re: How to combine rows into one

PGStats — Sun, 16 Oct 2016 03:27:53 GMT

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;

Re: How to combine rows into one

Crubal — Mon, 17 Oct 2016 13:50:28 GMT

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!

Re: How to combine rows into one

PGStats — Tue, 18 Oct 2016 03:46:20 GMT

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;

Re: How to combine rows into one

Crubal — Tue, 18 Oct 2016 13:27:55 GMT

Thanks PG, the do loop is pretty helpful!