How to roll up data from different columns into one row?

[LisaYIN9309]

Hi, I've come across this task where I want to transfer my data in this way:

My input data is structured like this:

Year	id	Code	Rate	A_15	A_16	B_15	B_16
2015	1	A	0.23	0.23
2016	1	A	0.22		0.22
2015	1	B	0.78			0.78
2016	1	B	0.79				0.79
2015	2	A	0.36	0.36
2016	2	A	0.19		0.19
2015	2	B	0.89			0.89
2016	2	B	0.83				0.83
2015	3	A	0.31	0.31
2016	3	A	0.34		0.34

I want to re-structure my data to look like this, I want each id has only one row, and row up multiple rows into one if it's under the same id - So year and code is now reflected in the column name and rate is the value in the corresponding column

id	A_15	A_16	B_15	B_16
1	0.23	0.22	0.78	0.79
2	0.36	0.19	0.89	0.83
3	0.31	0.34

I haven't figured out a way to do it tho, so any suggestion will be greatly appreciated!

Thank you for your time

[stat_sas · Accepted solution]

Hi,

Please try this.

proc sql;
create table want as
select id, max(A_15) as A_15, max(A_16) as A_16, max(B_15) as B_15, max(B_16) as B_16 from have
group by id;
quit;

[LisaYIN9309]

Thank you so much @stat_sas for your prompt reply, it worked!!!

[Reeza]

data want;
update have(obs=0 keep=(id a_15 a_16 b_15 b_16)) have (keep=(id a_15 a_16 b_15 b_16));

by id;
run;

You can use the UPDATE statement with OBS=0 trick to get this easily. 

I wouldn't recommend it though, it would be better IMO to reformat your data as:

data want;
set have;

Value = coalesce(a_15, a_16, b_15, b_16);

drop a_15 a_16 b_15 b_16;
run;

In this format you're keeping 'data' in your column names which is hard to make it dynamic. The code and the year are in the variable name. 

[art297]

I think the following also does what you want and is easier to code:

data want (drop=year code rate);
  update have (obs=0) have;
  by id;
run;

Art, CEO, AnalystFinder.com

[Cynthia_sas]

Hi,

  I would use either PROC REPORT or PROC TABULATE. Assuming you have the data in a file called WORK.TESTDATA, then something like this would work:

proc report data=testdata;
  column id A_15 A_16 B_15 B_16;
  define id / group;
  define a_15 / sum;
  define A_16 / sum;
  define b_15 / sum;
  define b_16 / sum;
run;
  
proc tabulate data=testdata;
  class id;
  var A_15 A_16 B_15 B_16;
  table id,
        A_15 A_16 B_15 B_16;
  keylabel sum=' ';
run;

cynthia