DATA Step, Macro, Functions and more

List option (proc freq) for percent outputs

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 113
Accepted Solution

List option (proc freq) for percent outputs

Hello,

 

I have 4 variables (sex, age, education, country) from two different sources. I would like to create a table to compare the distribution of education level (3 categories) by age and sex for every country.

My initial plan was to simply merge the output of proc freq using list option, such as:

 

proc freq data=data1;
table sex*cntry*grage*edu/list nofreq nopercent nocol;
run;

 

proc freq data=data2;
table sex*cntry*grage*edu/list nofreq nopercent nocol;
run;

 

Howewer, it seems that it is no possible to have percents in the output when we use /list, because only the frenquency appears.

When I remove /list, I have like 50 tables. What should I do?

 

Thank you.


Accepted Solutions
Solution
‎11-24-2016 04:07 AM
SAS Super FREQ
Posts: 8,868

Re: List option (proc freq) for percent outputs

Posted in reply to Demographer

Hi:

  I don't understand your explanation. Clearly in this test (with sashelp.cars, since you did not provide data), the output shows that percent statistics ARE created when the LIST option is used. Perhaps the results are not as you envisioned. Without seeing your original dataset, I don't know what you mean when you say: "The percents I want are those in row when there is no list".

 

Here's the output with LIST:

proc_freq_list_output.png

 

  Without the LIST option, my data for SASHELP.CARS is so sparse that the row percent doesn't make sense:

without_list.png

 

  It is possible that the output from PROC TABULATE might be more of what you wanted if you want the ROW percent. PROC TABULATE has statistics named ROWPCTN, ROWPCTSUM, COLPCTN and COLPCTSUM (just a few of the percent statistics) that might give you more control than PROC FREQ.

 

   cynthia

 

 

 

View solution in original post


All Replies
SAS Super FREQ
Posts: 8,868

Re: List option (proc freq) for percent outputs

Posted in reply to Demographer
Hi:
The NOPERCENT option is suppressing the percent. By default, the code you show will produce a report and you can't really merge a report. You do not show how you are creating an output dataset in order to do a merge. Did you post all the code. I would have expected to see an ODS OUTPUT statement or an OUT= option.

cynthia
Frequent Contributor
Posts: 113

Re: List option (proc freq) for percent outputs

Posted in reply to Cynthia_sas

The percents I want are those in row (i.g. when there is no/list) and not for the total (which is those elemitated by NOPERCENT option).

My problem is not in the merging of outputs, but rather in the production of right ouputs. I need something like that:

 

sex cntry grage edu Frequency Cumulative The column I need and that does't appear with /list
Frequency
0 AT 1 0 98.5714 98.5714 0.767921
0 AT 1 1 22.7654 121.337 0.177354
0 AT 1 2 7.02456 128.361 0.054725
0 AT 2 0 16.2753 144.637 0.120572
0 AT 2 1 86.303 230.94 0.639357
0 AT 2 2 32.4058 263.345 0.240071
0 AT 3 0 9.86456 273.21 0.069499
0 AT 3 1 79.6202 352.83 0.560947
0 AT 3 2 52.4542 405.284 0.369555
0 AT 4 0 13.3446 418.629 0.081016
0 AT 4 1 91.7042 510.333 0.556747
0 AT 4 2 59.6656 569.999 0.362237

 

Each 3 rows=100%.

Solution
‎11-24-2016 04:07 AM
SAS Super FREQ
Posts: 8,868

Re: List option (proc freq) for percent outputs

Posted in reply to Demographer

Hi:

  I don't understand your explanation. Clearly in this test (with sashelp.cars, since you did not provide data), the output shows that percent statistics ARE created when the LIST option is used. Perhaps the results are not as you envisioned. Without seeing your original dataset, I don't know what you mean when you say: "The percents I want are those in row when there is no list".

 

Here's the output with LIST:

proc_freq_list_output.png

 

  Without the LIST option, my data for SASHELP.CARS is so sparse that the row percent doesn't make sense:

without_list.png

 

  It is possible that the output from PROC TABULATE might be more of what you wanted if you want the ROW percent. PROC TABULATE has statistics named ROWPCTN, ROWPCTSUM, COLPCTN and COLPCTSUM (just a few of the percent statistics) that might give you more control than PROC FREQ.

 

   cynthia

 

 

 

Frequent Contributor
Posts: 113

Re: List option (proc freq) for percent outputs

[ Edited ]
Posted in reply to Cynthia_sas

Thanks. I did a Proc TABULATE with the option OUT and the statistic ROWPCTN and it worked.

 

Edit: There is a nother problem: it seems that we cannot use a weight statement with proc Tabulate.

SAS Super FREQ
Posts: 8,868

Re: List option (proc freq) for percent outputs

Posted in reply to Demographer
In fact, PROC TABULATE does support the WEIGHT statement, as documented here:
http://support.sas.com/documentation/cdl/en/proc/69850/HTML/default/viewer.htm#n1hdr41psnyg4en137jaa...

cynthia
Super User
Posts: 10,044

Re: List option (proc freq) for percent outputs

Posted in reply to Demographer
Use PROC SQL instead.

proc sql;
create table want as
 select make,model,type,origin,count(*) as freq,
  (select count(*) from sashelp.cars where 
  make=a.make and model=a.model and type=a.type)
  /count(*) as per format=percent8.2
  from sashelp.cars as a
   group by make,model,type,origin;
quit;

Super User
Posts: 10,044

Re: List option (proc freq) for percent outputs

Posted in reply to Demographer
OPPS.



proc sql;
create table want as
 select make,origin,model,count(*) as freq,
  count(*)/
  (select count(*) from sashelp.cars where 
  make=a.make and origin=a.origin )
   as per format=percent8.2
  from sashelp.cars as a
   group by make,origin,model;
quit;



☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 7 replies
  • 1181 views
  • 0 likes
  • 3 in conversation