Desktop productivity for business analysts and programmers

Make one row

Reply
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?

Grand Advisor
Posts: 10,235

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;

Grand Advisor
Posts: 17,419

Re: Make one row

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?

Valued Guide
Posts: 854

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: 854

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;

Grand Advisor
Posts: 9,593

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

Ask a Question
Discussion stats
  • 7 replies
  • 522 views
  • 1 like
  • 6 in conversation