topic Re: Proc Tabulate Help in SAS Procedures

Proc Tabulate Help

Mirisage — Thu, 05 Aug 2010 18:38:53 GMT

Hi Colleagues;

I have a material deprivation data set having over 18,000 observations. The two variables of interest are like below.

Data Deprivation;
Input CDN DQ;
Cards;
1350 2
2840 1
2745 3
9358 4
6570 2
1241 1
8759 5
3570 5

;
Run;

CDN = Census Division Number
DQ = Deprivation Quintile

Question
I need to categorize CDNs by DQs. In other words, I need to identify what are the CDAs falling under each deprivation quintile. For instance, the output should be like this.

DQ CDN
1 2840, 1241
2 1350, 6570
3 2745
4 9358
5 8759, 3570

I searched literature on “Proc Tabulate” for over 8 hours but could not find.

I wonder if anyone could help me to write the SAS code.

Thank you
Mirisage

Re: Proc Tabulate Help

sbb — Thu, 05 Aug 2010 18:50:14 GMT

If you only need a report, consider using PROC REPORT, with ACROSS.

If you need to generate a SAS data member as shown, consider using PROC TRANSPOSE.

Scott Barry
SBBWorks, Inc.

Re: Proc Tabulate Help

Cynthia_sas — Thu, 05 Aug 2010 19:39:39 GMT

Hi:
I agree with Scott. It is not clear whether you want a DATASET (with a single obs for every unique value of DQ) or whether you want a REPORT. The difference is that for a DATASET, you would probably have variable names such as CDN1, CDN2, CDN3, etc for every unique value of DQ. A PROC PRINT of such a dataset might look like this:
[pre]
Obs DQ cdn1 cdn2
1 1 2840 1241
2 2 1350 6570
3 3 2745 .
4 4 9358 .
5 5 8759 3570

[/pre]

In a report, as with PROC TABULATE or PROC REPORT on the other hand, the unique values of CDN would each become a column header and what would be inside the cells would be the calculated count for each crossing of CDN and DQ something like this (shown in LISTING output):
[pre]
+--------------------------------------------------------------------+
| | CDN | |
| +-------------------------------------------------------+ |
| | 1241 | 1350 | 2745 | 2840 | 3570 | 6570 | 8759 | 9358 |Total |
+-----+------+------+------+------+------+------+------+------+------+
|DQ | | | | | | | | | |
+-----+ | | | | | | | | |
|1 | 1| 0| 0| 1| 0| 0| 0| 0| 2|
+-----+------+------+------+------+------+------+------+------+------+
|2 | 0| 1| 0| 0| 0| 1| 0| 0| 2|
+-----+------+------+------+------+------+------+------+------+------+
|3 | 0| 0| 1| 0| 0| 0| 0| 0| 1|
+-----+------+------+------+------+------+------+------+------+------+
|4 | 0| 0| 0| 0| 0| 0| 0| 1| 1|
+-----+------+------+------+------+------+------+------+------+------+
|5 | 0| 0| 0| 0| 1| 0| 1| 0| 2|
+-----+------+------+------+------+------+------+------+------+------+
|Total| 1| 1| 1| 1| 1| 1| 1| 1| 8|
+--------------------------------------------------------------------+
[/pre]

If you want a dataset, then Scott's suggestion to use PROC TRANSPOSE is where you should look

If you want a report that can be routed to an HTML, RTF or PDF file, then I'm surprised that you didn't find at least this paper entitled, "Anyone Can Learn Proc Tabulate" http://www2.sas.com/proceedings/sugi27/p060-27.pdf ... especially with 8 hours of of searching. I found it with the Google search string:
SAS PROC TABULATE beginner

If you decide you want PROC TABULATE, then the above paper, plus these will be a place to start:
http://www2.sas.com/proceedings/forum2007/230-2007.pdf
http://www2.sas.com/proceedings/sugi28/071-28.pdf

cynthia

Re: Proc Tabulate Help

Mirisage — Thu, 05 Aug 2010 19:47:54 GMT

Hi Scott and Cynthia,

This is wealth of knowledge.And the links for more resources are also useful.

Thanks both of you.

I am customizing this knowledge for my specific question and would come back if I get stuck.

Thanks again!

Mirisage

I am