Contributor
Posts: 47

# Make one row

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?

Super User
Posts: 13,891

## Re: Make one row

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;

Super User
Posts: 23,959

## Re: Make one row

Yes, it's achievable.

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?

Valued Guide
Posts: 864

## Re: Make one row

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;

Contributor
Posts: 47

## Re: Make one row

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

Valued Guide
Posts: 864

## Re: Make one row

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;

Super User
Posts: 10,848

## Re: Make one row

Double proc transpose .

### Code: Program

`data have;infile cards dsd;input category\$ East2014 East20141 West2014 West20141;cards;Gender,235,125,145,129Goal,18,24,45,74Degree,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;`
Contributor
Posts: 52

## Re: Make one row

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;

Discussion stats
• 7 replies
• 642 views
• 1 like
• 6 in conversation