topic Re: Correct procedure for cross tab with headings only in SAS Procedures

Correct procedure for cross tab with headings only

MillerEL — Thu, 11 Mar 2021 16:43:53 GMT

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!

Re: Correct procedure for cross tab with headings only

Reeza — Thu, 11 Mar 2021 16:48:52 GMT

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.

Re: Correct procedure for cross tab with headings only

ballardw — Thu, 11 Mar 2021 16:56:34 GMT

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;

Re: Correct procedure for cross tab with headings only

MillerEL — Thu, 11 Mar 2021 17:01:24 GMT

@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!

Re: Correct procedure for cross tab with headings only

MillerEL — Thu, 11 Mar 2021 17:05:53 GMT

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!

Re: Correct procedure for cross tab with headings only

Reeza — Thu, 11 Mar 2021 17:15:05 GMT

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!

Re: Correct procedure for cross tab with headings only

MillerEL — Thu, 11 Mar 2021 17:54:58 GMT

@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?