Convert to wide format

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 9
Accepted Solution

Convert to wide format

I have a dataset in the following format: 

IDCountsState
299718FL
29974TX
299731NM
299717NY
299711GA
299832FL
299848NY
299836CA
29984GA
29984NM
299912FL
299942CA
299967GA
299923NM
29999TX

 

How do I manipulate it using proc SQL (etc.) if I want it like this?

 FLTXNMNYGANYCA
2997       
2998       
2999       

 

Thank you all in advance.


Accepted Solutions
Solution
2 weeks ago
Super User
Posts: 10,568

Re: Convert to wide format

proc transpose

by id 

var counts

id state

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code

View solution in original post


All Replies
Solution
2 weeks ago
Super User
Posts: 10,568

Re: Convert to wide format

proc transpose

by id 

var counts

id state

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Contributor VDD
Contributor
Posts: 27

Re: Convert to wide format

use proc transpose

 

Super User
Super User
Posts: 8,275

Re: Convert to wide format

Don't do it with PROC SQL. Use PROC TRANSPOSE.

Super User
Posts: 2,051

Re: Convert to wide format

data have;
input ID	Counts	State $;
cards;
2997	18	FL
2997	4	TX
2997	31	NM
2997	17	NY
2997	11	GA
2998	32	FL
2998	48	NY
2998	36	CA
2998	4	GA
2998	4	NM
2999	12	FL
2999	42	CA
2999	67	GA
2999	23	NM
2999	9	TX
;

proc transpose data=have out=want;
by id;
id state;
var counts;
run;
PROC Star
Posts: 1,400

Re: Convert to wide format

I think most users in here think you should use Proc Transpose Smiley Happy

 

But why on earth would you want to use Proc SQL anyway?

Super User
Posts: 2,051

Re: Convert to wide format

proc sql;
select distinct state into :state separated by ' '
from have;
quit;

%put &state;


data want1;
do _n_=1 by 1 until(last.id);
set have;
by id;
array st &state;
if _n_=1 then call missing(of st(*));
do _i_=1 to dim(st);
if vname(st(_i_))=state then st(_i_)=counts;
end;
end;
run;


PROC Star
Posts: 549

Re: Convert to wide format

As everyone suggested, this is easy and clean using PROC transpose. But if you want to do in PROC sql. you can do it if you have one distinct state per id by lot of hard coding as shown below

 

proc sql;
create table want as 
select id,
       max( case when state = "FL" then counts else 0 end) as FL,
	   max( case when state = "TX" then counts else 0 end) as TX,
	   max( case when state = "NM" then counts else 0 end) as NM,
	   max( case when state = "NY" then counts else 0 end) as NY,
	   max( case when state = "GA" then counts else 0 end) as GA,
	     max( case when state = "CA" then counts else 0 end) as CA
from have
group by 1;
Occasional Contributor
Posts: 9

Re: Convert to wide format

[ Edited ]

Thank you for all your quick and helpful responses. The proc transpose threw up some error messages saying ' The ID value "FL" occurs twice in the same BY group'.

 

The PROC SQL worked fine for this purpose. I didn't try the second suggested SQL procedure since the first worked for my data.

 

proc sql;
select distinct state into :state separated by ' '
from have;
quit;

%put &state;

data want1;
do _n_=1 by 1 until(last.id);
set have;
by id;
array st &state;
if _n_=1 then call missing(of st(*));
do _i_=1 to dim(st);
if vname(st(_i_))=state then st(_i_)=counts;
end;
end;
run;

I appreciate your efforts. Thank you all once again.

Super User
Posts: 8,216

Re: Convert to wide format

@vdfdd: Check your input dataset! Sounds like you have multiple records for one or more IDs for at least one of your by groups.

 

Either that's in error, or you have to sum the same-state records before transposing the dataset.

 

Art, CEO, AnalystFinder.com

 

Super User
Posts: 10,568

Re: Convert to wide format

This error message from proc transpose also means that your results from the other approaches will not be correct, as values will be overwritten and lost. Another reason why proc transpose is THE tool for this, as it will alert you.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Occasional Contributor
Posts: 9

Re: Convert to wide format

Posted in reply to KurtBremser

 When I added the Let option to the proc transpose, I got the following:

 

WARNING: 4 observations omitted due to missing ID values.
NOTE: There were 99187 observations read from the data set WORK.CLEANY.
NOTE: The data set WORK.WANTY has 26568 observations and 38 variables.

 

The same number of observations as the proc sql. Any thought on this?

 

Super User
Posts: 10,568

Re: Convert to wide format

So you have missing ID values. Decide how you want to treat those.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 12 replies
  • 183 views
  • 2 likes
  • 8 in conversation