BookmarkSubscribeRSS Feed
Mirisage
Obsidian | Level 7
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
3 REPLIES 3
sbb
Lapis Lazuli | Level 10 sbb
Lapis Lazuli | Level 10
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.
Cynthia_sas
SAS Super FREQ
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
Mirisage
Obsidian | Level 7
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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

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