How to create a cross sectional table

[peiy5920]

                                               PCR_URINE_COMBINE

                      First Tertile             Second Tertile        Third Tertile

                      Mean (se)/N(%)      Mean (se)/N(%)      Mean (se)/N(%)

White(%)

Male(%)

Age(yrs)

EGFR(%)

>60

45-60

30-45

<30

Hi all,

This is my table and I want to create a cross sectional table.

About the variable PCR_URINE_COMBINE, my code is: 

proc univariate data=PROJECT.PAPER_CRIC; var PCR_URINE_COMBINED; output out=perc pctlpre=p_ pctlpts= 33.33 66.67; run;

I knew the two points of this variable(0.062761506, 0.225225225 ) from the sas output, and next I need to divide into three parts (first,2nd,3rd)of this variable, that is from continuous variable to categorical variable. I read the message from you told me (like proc rank....), but i am still confused how to produce my table (Like  proc means,   proc freq....). I write some codes, but i don't know how to write more...Can someone help revise my codes or give some sample code for me? Sorry for my rough draft of codes. I really appreciate your help.

data CRIC;
set PROJECT.PAPER_CRIC;

if WHITE='White' and SEX='Male';

format group $8.;

if EGFR_CKD_EPI<30 then
group='<30';

if EGFR_CKD_EPI>=30 and EGFR_CKD_EPI<45 then
group='30-45';

if EGFR_CKD_EPI>=45 and EGFR_CKD_EPI<60 then
group='45-60';

if EGFR_CKD_EPI>=60 then
group='>60';

ATTRIB Tertile  LENGTH = 8  LABEL = "Tertile";

if PCR_URINE_COMBINED< 0.062761506 then Tertile=1;
else if PCR_URINE_COMBINED=0.062761506-0.225225225 then Tertile=2;
else if PCR_URINE_COMBINED> 0.225225225 then Tertile=3;
run;

proc rank data=PROJECT.PAPER_CRIC groups=3 out=want;
var PCR_URINE_COMBINED;
ranks tertiles;
run;

[ballardw · Accepted solution]

Data is nice. Example data in the form of data step is best.

Instructions here: https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat... will show how to turn an existing SAS data set into data step code that can be pasted into a forum code box using the {i} icon or attached as text to show exactly what you have and that we can test code against.

Did you run that data step and look at results?

I suspect that you have nearly one third of your Tertile values may be missing because in this  code:

if PCR_URINE_COMBINED< 0.062761506 then Tertile=1;
else if PCR_URINE_COMBINED=0.062761506-0.225225225 then Tertile=2;
else if PCR_URINE_COMBINED> 0.225225225 then Tertile=3;

the

=0.062761506-0.225225225 

is NOT a range of values.

Consider this example data set:

data example;
   input PCR_URINE_COMBINED;
   if PCR_URINE_COMBINED< 0.062761506 then Tertile=1; 
   else if PCR_URINE_COMBINED=0.062761506-0.225225225 then Tertile=2; 
   else if PCR_URINE_COMBINED> 0.225225225 then Tertile=3;
datalines;
0.062761
0.062761506
0.062762
0.225225225
0.225226
;

and see if the Tertile values are as expected.

SAS does allow use of code like

   else if 0.062761506 le PCR_URINE_COMBINED le 0.225225225 then Tertile=2; 

where LE is "less than or equal" to indicate a range of values.

When you show a table such as your "want to create" it really helps to provide example starting data and the expected values.

I am not actually sure what your " Mean (se)/N(%)" is supposed to be. Mean of what? SE I would typically expect to be a standard error, if so of what? Since your rows have % then it really is hard to envision what is meant by the column headings especially without any data.

Proc Rank in this case is likely in the wrong place. That would be used on the raw data BEFORE adding your TERTILE variable and could be used to create a variable indicating which group a variable is in based on the values to create the Tertiles.

A brief example using data you should have available:

proc rank data=sashelp.class groups=3 
   out=work.agetertiles;
   var age;
   ranks agetertile;
run;

And creating a report with the agetertiles as columns and count and percents of sex within the column

proc tabulate data=work.agetertiles;
   class agetertile;
   class sex;
   tables sex,
          agetertile*(colpctn n)
   ;
run;

Note that Proc Rank starts at 0 not 1 for the ranks. So to get 1, 2 and 3 you would need to do something such as pass the data through a data step before the report procedure.

[peiy5920]

Thanks for your detailed reply!