Solved
Contributor
Posts: 38

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

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!

Accepted Solutions
Solution
‎12-28-2017 04:48 PM
Posts: 1,270

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

Hi,

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;

All Replies
Solution
‎12-28-2017 04:48 PM
Posts: 1,270

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

Hi,

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;

Contributor
Posts: 38

Super User
Posts: 23,958

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

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

Super User
Posts: 8,214

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

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

SAS Super FREQ
Posts: 9,423

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

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

☑ This topic is solved.