BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Demographer
Pyrite | Level 9

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Cynthia_sas
SAS Super FREQ

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

7 REPLIES 7
Cynthia_sas
SAS Super FREQ
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
Pyrite | Level 9

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
SAS Super FREQ

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

 

 

 

Demographer
Pyrite | Level 9

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
SAS Super FREQ
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
Super User
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
Super User
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;



sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 7 replies
  • 16568 views
  • 1 like
  • 3 in conversation