topic Transposing values in SAS Programming

Transposing values

Sandy10 — Fri, 27 Jul 2018 10:28:15 GMT

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

Re: Transposing values

Kurt_Bremser — Fri, 27 Jul 2018 10:36:49 GMT

Sort by id;

Use proc transpose with

by id;

var value;

id category;

Re: Transposing values

Astounding — Fri, 27 Jul 2018 12:52:35 GMT

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;

Re: Transposing values

novinosrin — Fri, 27 Jul 2018 14:02:06 GMT

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