topic Re: List option (proc freq) for percent outputs in SAS Programming

List option (proc freq) for percent outputs

Demographer — Wed, 23 Nov 2016 14:10:47 GMT

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.

Re: List option (proc freq) for percent outputs

Cynthia_sas — Wed, 23 Nov 2016 14:34:13 GMT

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

Re: List option (proc freq) for percent outputs

Demographer — Wed, 23 Nov 2016 14:42:19 GMT

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
sex	cntry	grage	edu	Frequency	Frequency	The column I need and that does't appear with /list
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%.

Re: List option (proc freq) for percent outputs

Cynthia_sas — Wed, 23 Nov 2016 19:58:27 GMT

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:

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

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

Re: List option (proc freq) for percent outputs

Ksharp — Thu, 24 Nov 2016 06:07:07 GMT

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;

Re: List option (proc freq) for percent outputs

Ksharp — Thu, 24 Nov 2016 06:22:37 GMT

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;

Re: List option (proc freq) for percent outputs

Demographer — Thu, 24 Nov 2016 09:34:26 GMT

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.

Re: List option (proc freq) for percent outputs

Cynthia_sas — Thu, 24 Nov 2016 15:48:19 GMT

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#n1hdr41psnyg4en137jaap5ia951.htm

cynthia