Correct procedure for cross tab with headings only

[MillerEL]

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]

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]

---

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

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]

---

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

---

[ballardw]

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]

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!