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;
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
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.