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

[Yoko]

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

[HB]

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?

[ballardw · Accepted solution]

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;