My data set is :
category East2014 East20141 West2014 West20141
Gender 235 125 145 129
Goal 18 24 45 74
Degree 14 412 87 35
I need to change like this
Category 2014 2015 Place
Gender 235 145 East
Goal 18 24 East
Degree 14 412 East
Gender 145 129 West
Goal 45 74 West
Degree 87 35 West
Is that achievable?
You'll have to have a variable name other than 2014 or 2015 unless you want to deal with non-standard variable names but:
data want (keep=category Y2014 Y2015 Place);
set have;
Y2014=East2014; Y2015 = East2015; Place='East'; Output;
Y2014=West2014; Y2015 = West2015; Place='West'; Output;
run;
Yes, it's achievable.
Try Tasks>Data>Transpose
You may need to do it twice and merge or split the data again somehow.
Is your sample data correct, should it be East2014 and East20141 or East2015?
Here is a solution. Looks like you have a typo in your output. Should Gender 2015 East be 125? If so this works. I don't see what order you have this sorted in but this is pretty close:
data have;
infile cards dsd;
input category$ East2014 East20141 West2014 West20141;
cards;
Gender,235,125,145,129
Goal,18,24,45,74
Degree,14,412,87,35
;
run;
proc transpose data=have out=tran;id category;var East2014 East20141 West2014 West20141;
proc transpose data=tran out=tran2;id _name_;
proc sql;
create table want as
select _NAME_ as Category,east2014 as '2014'n, east20141 as '2015'n,'East' as Place
from tran2
union
select _NAME_ as Category,west2014 as '2014'n, west20141 as '2015'n,'West' as Place
from tran2
order by Place,'2014'n desc;
it works, thank you. But the problem is i have bigger than this table. And it is not same order as before? I want to same order as before
I mean row order
Gender, Goal, Degree,.
I don't see any way to do it other than numbering them or giving them logical alphabetical prefixes. I'm interested to see if there is another way. If you copy this to the bottom of the previous code it will get you what you want.
data want_order;
set want;
if category = 'Gender' then category = '1 Gender';
if category = 'Goal' then category = '2 Goal';
if category = 'Degree' then category = '3 Degree';
run;
proc sort data=want_order;by place category;
data want_final;
set want_order;
category = compress(category,1,'d');
run;
Double proc transpose .
data have;
infile cards dsd;
input category$ East2014 East20141 West2014 West20141;
cards;
Gender,235,125,145,129
Goal,18,24,45,74
Degree,14,412,87,35
;
run;
proc transpose data=have out=temp;
by category notsorted;
var east: west:;
run;
data temp;
set temp;
length location year $ 40;
location=compress(_name_,,'ka');
year=compress(_name_,,'kd');
drop _name_;
run;
proc transpose data=temp out=want(drop=_name_);
by category location notsorted;
id year;
var col1;
run;
I would add to xia's solution:
data want;
set want;
retain zRnk;
if category=lag(category) then do; zRnk+1; end;
run;
proc sort data=want out=want(drop=zRnk); by location zRnk; run;
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.
What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.
Find more tutorials on the SAS Users YouTube channel.