List option (proc freq) for percent outputs

[Demographer]

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.

[Cynthia_sas]

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

[Demographer]

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%.

[Cynthia_sas · Accepted solution]

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

[Demographer]

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.

[Cynthia_sas]

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

[Ksharp]

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;

[Ksharp]

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;