BookmarkSubscribeRSS Feed
lerdem
Quartz | Level 8

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?

7 REPLIES 7
ballardw
Super User

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;

Reeza
Super User

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?

Steelers_In_DC
Barite | Level 11

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;

lerdem
Quartz | Level 8

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

Steelers_In_DC
Barite | Level 11

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;

Ksharp
Super User

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;
billfish
Quartz | Level 8

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;

sas-innovate-2024.png

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.

 

Register now!

SAS Enterprise Guide vs. SAS Studio

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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