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
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;
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;
Thank you so much @stat_sas for your prompt reply, it worked!!!
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.
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
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
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.