BookmarkSubscribeRSS Feed
Sandy10
Calcite | Level 5

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

3 REPLIES 3
Astounding
PROC Star

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;

novinosrin
Tourmaline | Level 20

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 @Kurt_Bremser suggestion earlier making my post just redundant 

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!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

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
  • 3 replies
  • 623 views
  • 2 likes
  • 4 in conversation