topic Re: Make one row in SAS Enterprise Guide

Make one row

lerdem — Thu, 30 Jul 2015 15:56:47 GMT

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?

Re: Make one row

ballardw — Thu, 30 Jul 2015 16:17:09 GMT

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;

Re: Make one row

Reeza — Thu, 30 Jul 2015 16:19:51 GMT

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?

Re: Make one row

Steelers_In_DC — Thu, 30 Jul 2015 16:20:28 GMT

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;

Re: Make one row

lerdem — Thu, 30 Jul 2015 17:29:53 GMT

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

Re: Make one row

Steelers_In_DC — Thu, 30 Jul 2015 17:54:09 GMT

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;

Re: Make one row

Ksharp — Fri, 31 Jul 2015 14:52:51 GMT

Double proc transpose .

Code: Program

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;

Re: Make one row

billfish — Fri, 31 Jul 2015 23:35:51 GMT

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;