topic summing row by 3 variables, then changing to a long horizontal data in SAS Programming

summing row by 3 variables, then changing to a long horizontal data

Yoko — Mon, 01 Oct 2018 18:04:55 GMT

Hello,

I created a sample data set using this:

DATA hosp;
input id_i id_m days category;
DATALINES;
. 4 13 7
. 4 5 7
1 . 2 1
1 . 108 13
2 . 14 2
. 3 8 2
. 3 8 12
;
run;

proc sql;
create table days_sum as select *,
sum (days) as days1995
from hosp
group by id_i, id_m,category;
quit;

Table: days spent at hospital for different reasons (category)

Obs	id_i	id_m	days	category	days1995
1	.	3	8	2	8
2	.	3	8	12	8
3	.	4	5	7	18
4	.	4	13	7	18
5	1	.	2	1	2
6	1	.	108	13	108
7	2	.	14	2	14

Now, I want this table to look like this (I just want to show you id_i=1 and id_m=4 as examples):

Table: days spent at hospital for different reasons (category)

id_i	id_m	category1	category2	…	category7	category13
1	.	2	.	.	.	.	108
.	.	.	.	.	.	.	.
.	4	.	.	.	18	.	.

Could anyone tell me how to get the table shown above?

Thank you,

Yoko

Re: summing row by 3 variables, then changing to a long horizontal data

HB — Mon, 01 Oct 2018 19:29:39 GMT

Before you go denormalizing your data in order to solve some other problem you haven't told us about, maybe you could tell us what you are really trying to do?

Re: summing row by 3 variables, then changing to a long horizontal data

ballardw — Mon, 01 Oct 2018 19:32:08 GMT

First is do you know what the maximum number of categories might be?

I think you should go ahead an show all the desired output for that input. There may be some issues around combinations of Id_i and id_m that aren't obvious.

Also, what do you want to have happen with the days variable?

This may get you started. However since you already have a variable named category you cannot name an array the same. There are a number of ways around that but that is a fiddly bit if the logic works. If you expect more values of category change the 13 to the largest integer number.

data want;
   set work.days_sum;
   array cat{13};
   retain cat1-cat13;
   by id_i id_m ;
   if first.id_m then do;
      call missing(of cat(*));
   end;
   cat[category]=days1995;
   if last.id_m;
run;