topic Re: Creating a table: which function to use? in SAS Procedures

Creating a table: which function to use?

deleted_user — Mon, 25 May 2009 02:34:11 GMT

Hello!

I have 45 variables called TEST (TEST1-TEST45) and 6 variables called RACE_CAT (RACE_CAT1-RACE_CAT6). There are approximately 900 observations in my dataset.
I need to create a table showing the counts of each and every test per race category; and I also need to calculate the rates of each test per 10,000 people/per race category.

Being a novice, I am having a hard time deciding between proc sql or using a multidimensional array. Do I need to calculate the totals for each variable beforehand to calculate the rates, or is there a way to do this in the process of creating the table??

I have tried to simplify my data as best as I can; all are coded as "1" if "yes" for each test or race category.

Can anyone help me??
Thanks 🙂

Re: Creating a table: which function to use?

LinusH — Tue, 26 May 2009 05:54:04 GMT

I would try to transpose the data, so that you'll have one test per row.
Don't exactly understand how you want to report on race, maybe you could attach some sample input and desired output to describe the problem?

/Linus

Re: Creating a table: which function to use?

deleted_user — Tue, 26 May 2009 15:27:29 GMT

Hi Linus, thanks for the response. The data is already transposed with one record per subject (every subject has 45 tests and 6 race category variables). I ended up just making 6 separate datasets per race category and importing it into Excel to do the calculations, it was just easier. But in the future, I would like to be able to create a table in SAS using some sort of Array or Proc Sql function....

Anyhow, thanks 🙂

Re: Creating a table: which function to use?

ChrisNZ — Tue, 26 May 2009 22:15:54 GMT

Unsure I fully understand you goal, you might need to provide more details.

Linus' suggestion is good: in general, transposing the dataset (for example to have one record per figure), makes it easy to summarise it in funny ways.
[pre]
TEST# CAT# RESULT
A x 3
B x 6
....
[/pre]

Re: Creating a table: which function to use?

sbb — Wed, 27 May 2009 00:21:12 GMT

It would be more like convert your horizontal SAS file to vertical, using a set of arrays to control an outer and inner loop, creating an output observation for each unique value combination based on some key variable list.

Scott Barry
SBBWorks, Inc.

ON DATA TRANSFER - topic "HORIZONTAL TO VERTICAL DATA FORMAT":
Aileen L. Yam, Clinical Information Analysis, Inc., Plainsboro, NJ

http://www2.sas.com/proceedings/sugi23/Advtutor/p46.pdf