BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
vdfdd
Fluorite | Level 6

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.

1 ACCEPTED SOLUTION
12 REPLIES 12
VDD
Ammonite | Level 13 VDD
Ammonite | Level 13

use proc transpose

 

Tom
Super User Tom
Super User

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

novinosrin
Tourmaline | Level 20
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;
PeterClemmensen
Tourmaline | Level 20

I think most users in here think you should use Proc Transpose 🙂

 

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

novinosrin
Tourmaline | Level 20
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;


kiranv_
Rhodochrosite | Level 12

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;
vdfdd
Fluorite | Level 6

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.

art297
Opal | Level 21

@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

 

Kurt_Bremser
Super User

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.

vdfdd
Fluorite | Level 6

 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?

 

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
  • 12 replies
  • 1390 views
  • 2 likes
  • 8 in conversation