DATA Step, Macro, Functions and more

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

Accepted Solution Solved
Reply
Contributor
Posts: 38
Accepted Solution

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:

YearidCodeRateA_15A_16B_15B_16
20151A0.230.23   
20161A0.22 0.22  
20151B0.78  0.78 
20161B0.79   0.79
20152A0.360.36   
20162A0.19 0.19  
20152B0.89  0.89 
20162B0.83   0.83
20153A0.310.31   
20163A0.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

idA_15A_16B_15B_16
10.230.220.780.79
20.360.190.890.83
30.310.34  

 

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

Thank you for your time

 


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

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

Posted in reply to LisaYIN9309

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;

View solution in original post


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

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

Posted in reply to LisaYIN9309

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;

Contributor
Posts: 38

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

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

Super User
Posts: 23,958

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

Posted in reply to LisaYIN9309
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?

Posted in reply to LisaYIN9309

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?

Posted in reply to LisaYIN9309

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.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 451 views
  • 0 likes
  • 5 in conversation