DATA Step, Macro, Functions and more

Transposing values

Reply
Contributor
Posts: 40

Transposing values

Hi,

 

I have below data :

 

ID Category   Value

1   Income       100

2   Income       200

1   Country      India

2   Country      Australia

3   Country     Canada            

 

I need output like below;

 

ID Income   Country

1  100         India 

2  200         Australia

3                 Canada

 

Please advise how to do this in SAS. Thanks

Super User
Posts: 10,590

Re: Transposing values

Sort by id;

Use proc transpose with

by id;

var value;

id category;

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Super User
Posts: 6,935

Re: Transposing values

Right now, the income values have to be character because VALUE also contains character values.  If you want INCOME to be numeric, you will need a DATA step:

 

proc sort data=have;

by id;

run;

 

data want;

set have;

by ID;

retain income country;

if category = "Country" then Country = Value;

else if category = "Income" then income = input(value, 8.);

if last.ID;

output;

income = .;

country = " ";

run;

Super User
Posts: 2,062

Re: Transposing values

[ Edited ]

Hi @Sandy10 seems a straight forward transpose after sorting:

 

data have;
input ID  Category $  Value :$10.;
cards;
1   Income       100
2   Income       200
1   Country      India
2   Country      Australia
3   Country     Canada 
;

proc sort data=have;
by id category;
run;
proc transpose data=have out=want(drop=_name_);
by id;
var  value;
id category;
run;

 

EDIT: Oh, well. I missed to notice @KurtBremser suggestion earlier making my post just redundant 

Ask a Question
Discussion stats
  • 3 replies
  • 56 views
  • 2 likes
  • 4 in conversation