BookmarkSubscribeRSS Feed
MillerEL
Obsidian | Level 7

Hello,

 

Can someone help me determine the best procedure for creating a crosstab with variable names instead of counts/sums/etc.

 

For Example, I can do a PROC FREQ of _Region* Area to get this output:

 Area1Area2Area3Area4
Region10410
Region25200
Region33006

 

But what I want is this:

Region1Area2Area3
Region2Area1Area2
Region3Area1Area4

 

I have 15x115 variables, and the first output is too cumbersome and requires a lot of Excel manipulation.

Is there a procedure/option/format that will help me to create an output that looks like the second?

I'm certain I must be overthinking this, but I'm just stuck.

 

Thank you!

6 REPLIES 6
Reeza
Super User
What does your original table look like? I think it may need some data manipulation, proc transpose or proc freq to get there but it's likely possible.
MillerEL
Obsidian | Level 7

@Reeza wrote:
What does your original table look like? I think it may need some data manipulation, proc transpose or proc freq to get there but it's likely possible.

The original table would look something like this:

Obs_region_area
112
234
321
413
522
634
731

 

This is made up, of course, but you get the idea. I'm actually working with groups of FIPS codes. I want to know which FIPS are in each region, not how many records are assigned to the FIPS. The _region variable in my dataset is added by another party, so I'm trying to check the _region variable to make sure it contains the correct FIPS assignments. 

 

Proc Freq will give me the first output in my original post (no of records assigned to each FIPS by region). But I can't figure out how to get the second (FIPS code assignment by region). I can do the first and then manipulate the data in Excel. I'm just trying to avoid the time consuming Excel portion.

 

Thanks!

Reeza
Super User

A transpose works fine for me. 

 

data have;
infile cards dlm='09'x;
input Obs	_region	_area;
cards;
1	1	2
2	3	4
3	2	1
4	1	3
5	2	2
6	3	4
7	3	1
;;;

proc sort data=have; 
by _region;
run;

proc transpose data=have out=want prefix=AreaID;
by _region;
var _area;
run;

@MillerEL wrote:

@Reeza wrote:
What does your original table look like? I think it may need some data manipulation, proc transpose or proc freq to get there but it's likely possible.

The original table would look something like this:

Obs _region _area
1 1 2
2 3 4
3 2 1
4 1 3
5 2 2
6 3 4
7 3 1

 

This is made up, of course, but you get the idea. I'm actually working with groups of FIPS codes. I want to know which FIPS are in each region, not how many records are assigned to the FIPS. The _region variable in my dataset is added by another party, so I'm trying to check the _region variable to make sure it contains the correct FIPS assignments. 

 

Proc Freq will give me the first output in my original post (no of records assigned to each FIPS by region). But I can't figure out how to get the second (FIPS code assignment by region). I can do the first and then manipulate the data in Excel. I'm just trying to avoid the time consuming Excel portion.

 

Thanks!


 

MillerEL
Obsidian | Level 7

 


@Reeza wrote:

A transpose works fine for me. 

 

data have;
infile cards dlm='09'x;
input Obs	_region	_area;
cards;
1	1	2
2	3	4
3	2	1
4	1	3
5	2	2
6	3	4
7	3	1
;;;

proc sort data=have; 
by _region;
run;

proc transpose data=have out=want prefix=AreaID;
by _region;
var _area;
run;


Maybe I just haven't used Proc Transpose enough, but it doesn't give me this:

Region1Area2Area3
Region2Area1Area2
Region3Area1Area4

It gives me this:
Region1Area2Area2Area2Area2Area3    
Region2Area1Area1Area1Area1Area1Area2Area2  
Region3Area1Area1Area1Area4Area4Area4Area4Area4Area4

Is there an option that would group the areas?

 

ballardw
Super User

Example data and what the desired result would be for that data.

 

You may not be aware of the options for Proc freq.

 

Try

Proc freq data=<your dataset>;

   tables region * area / list;

run;

MillerEL
Obsidian | Level 7

The output I am looking for is posted in my query. I posted a sample of what the data would look like another response.

The Proc Freq /list option does not provide the desired outcome, but it is closer!


Thank you!

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

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
  • 6 replies
  • 655 views
  • 0 likes
  • 3 in conversation