topic Re: How to generate weighted count and column percentage from proc tabulate in SAS Studio in SAS Programming

How to generate weighted count and column percentage from proc tabulate in SAS Studio

Crystal_F — Sun, 11 Sep 2022 22:37:00 GMT

Hi everyone,

I'm having a hard time editing the following code to generate weighted N and column percentage using proc tabulate. My dataset is survey design but only has weights and no strata nor cluster. My expected result is to have weighted frequency of lab test outcome by condition and race group.

Using survey frequency is less flexible compared to tabulate in term of table format.

Any help on rewriting the code is greatly appreciated!

proc tabulate data=test(where=(cohort=1)) noseps  ;
class flag_A1C9_lst  Cat_raceeth obesity Hyperlipidemia Hypertension ;
table flag_A1C9_lst,(all  obesity*Cat_raceeth 
					 Hyperlipidemia*Cat_raceeth 
					 Hypertension*Cat_raceeth)*(n*f=comma8. colPctN*f=4.1);				 
run;

Re: How to generate weighted count and column percentage from proc tabulate in SAS Studio

mkeintz — Mon, 12 Sep 2022 01:39:13 GMT

And what is the weight variable?

Please show some sample data, in the form of a working data step.

And the expected result.

Re: How to generate weighted count and column percentage from proc tabulate in SAS Studio

Crystal_F — Mon, 12 Sep 2022 14:19:35 GMT

data sample;
  input uniqueid $ flag_A1C9_lst Cat_raceeth obesity Hyperlipidemia Hypertension cohort FWGT_2019
        ;
datalines;
1 1 1 1 1 0 1 1473
2 0 2 0 1 1 0 2296
3 . 3 1 0 1 1 7484
;
run;

The expected output looks like below:

Thank you for your help!

Re: How to generate weighted count and column percentage from proc tabulate in SAS Studio

ballardw — Mon, 12 Sep 2022 14:58:19 GMT

What do you get when you add a Weight statement to your proc tabulate:

proc tabulate data=test(where=(cohort=1)) noseps  ;
class flag_A1C9_lst  Cat_raceeth obesity Hyperlipidemia Hypertension ;
weight FWGT_2019;
table flag_A1C9_lst,(all  obesity*Cat_raceeth 
					 Hyperlipidemia*Cat_raceeth 
					 Hypertension*Cat_raceeth)*(n*f=comma8. colPctN*f=4.1);				 
run;

If your variable FWGT_2019 is actually a count variable, such as from summarizing data, you may actually want a FREQ statement instead of weight.

Re: How to generate weighted count and column percentage from proc tabulate in SAS Studio

Crystal_F — Mon, 12 Sep 2022 17:39:44 GMT

Thank you for your reply! Adding weight statement alone couldn't generate weighted N and weighted column percentage from 'n*f=comma8. colPctN*f=4.1' in SAS Studio.

Re: How to generate weighted count and column percentage from proc tabulate in SAS Studio

Reeza — Mon, 12 Sep 2022 17:44:49 GMT

FREQ seems to work?

data sample;
  input uniqueid $ flag_A1C9_lst Cat_raceeth obesity Hyperlipidemia Hypertension cohort FWGT_2019
        ;
datalines;
1 1 1 1 1 0 1 1473
2 0 2 0 1 1 0 2296
3 . 3 1 0 1 1 7484
;
run;

 

proc tabulate data=sample(where=(cohort=1)) noseps  ;
class flag_A1C9_lst  Cat_raceeth obesity Hyperlipidemia Hypertension / missing ;
freq FWGT_2019;
table flag_A1C9_lst,(all  obesity*Cat_raceeth 
					 Hyperlipidemia*Cat_raceeth 
					 Hypertension*Cat_raceeth)*(n*f=comma8. colPctN*f=4.1);				 
run;

Re: How to generate weighted count and column percentage from proc tabulate in SAS Studio

Crystal_F — Tue, 13 Sep 2022 19:00:34 GMT

Yes, using freq statement instead really works. Thank you @ballardw @Reeza for your great suggestions!