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
Sort by id;
Use proc transpose with
by id;
var value;
id category;
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;
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
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.