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:
Area1 | Area2 | Area3 | Area4 | |
Region1 | 0 | 4 | 1 | 0 |
Region2 | 5 | 2 | 0 | 0 |
Region3 | 3 | 0 | 0 | 6 |
But what I want is this:
Region1 | Area2 | Area3 |
Region2 | Area1 | Area2 |
Region3 | Area1 | Area4 |
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!
@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!
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!
@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:
Region1 Area2 Area3 Region2 Area1 Area2 Region3 Area1 Area4
It gives me this:
Region1 Area2 Area2 Area2 Area2 Area3 Region2 Area1 Area1 Area1 Area1 Area1 Area2 Area2 Region3 Area1 Area1 Area1 Area4 Area4 Area4 Area4 Area4 Area4
Is there an option that would group the areas?
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;
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!
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 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.