topic Re: proc freq crosstab in SAS Programming

proc freq crosstab

xiaopingnie — Wed, 23 Jun 2021 18:53:28 GMT

Hi,

I just met a problem when using proc freq crosstab.

for example

PROC SQL;
create table CARS1 as
SELECT make, type, invoice, horsepower, length, weight
   FROM 
   SASHELP.CARS
   WHERE make in ('Audi','BMW')
;
RUN;

proc FREQ data = CARS1;
tables make*type; 
run;

without any suppression, I only got two rows in each cells instead of 4 rows. also there's a warning "WARNING: Format pctfmt. failed to load!" in the log window. But this warning did not show up when I ran one way proc freq.
Any one know what's going on here?

	SUV	Sedan	Sports	Wagon	Total
Audi	0	13	4	2	19
0	68.42	21.05	10.53
BMW	2	13	4	1	20
10	65	20	5
Total	2	26	8	3	39

Re: proc freq crosstab

data_null__ — Wed, 23 Jun 2021 19:06:03 GMT

@xiaopingnie wrote:

Hi,

I just met a problem when using proc freq crosstab.

for example
PROC SQL;
create table CARS1 as
SELECT make, type, invoice, horsepower, length, weight
   FROM 
   SASHELP.CARS
   WHERE make in ('Audi','BMW')
;
RUN;

proc FREQ data = CARS1;
tables make*type; 
run;
without any suppression, I only got two rows in each cells instead of 4 rows. also there's a warning "WARNING: Format pctfmt. failed to load!" in the log window. But this warning did not show up when I ran one way proc freq.
Any one know what's going on here?

SUV Sedan Sports Wagon Total

Audi 0 13 4 2 19

0 68.42 21.05 10.53

BMW 2 13 4 1 20

10 65 20 5

Total 2 26 8 3 39

I just ran your code and got this. Tell more of the story.

Re: proc freq crosstab

ballardw — Wed, 23 Jun 2021 20:03:26 GMT

Just as an aside, most procedures will support a where statement and there wouldn't be a need to create that extra data set:

proc FREQ data = sashelp.cars;
   where make in ('Audi' ,'BMW');
   tables make*type; 
run;

"WARNING: Format pctfmt. failed to load!" would likely only occur if you attempted to assign that format to a variable. The only references I find quickly for Pctfmt is in Proc Tabulate documentation using a custom format of that name.

If the data set has a variable with that format already assigned but the format is not available in current session you might get that message.Which would not happen with the shown code unless you have somehow modified the SASHELP.Cars data set or perhaps one of the table Templates that Proc Freq uses.

Re: proc freq crosstab

Cynthia_sas — Wed, 23 Jun 2021 20:17:58 GMT

Hi:

I agree with previous posters. I do not observe the results that were originally posted.

No matter whether I use the original code or use streamlined code, the results are the same.

Cynthia

Re: proc freq crosstab

xiaopingnie — Wed, 23 Jun 2021 20:31:12 GMT

It’s not code issue. I just use default proc freq crosstab, what ever data I use, I just got two rows instead of 4 rows. Yes, I did not use suppression, but can not get percent and column percent.. please help!

Re: proc freq crosstab

ballardw — Wed, 23 Jun 2021 21:37:05 GMT

@xiaopingnie wrote:
It’s not code issue. I just use default proc freq crosstab, what ever data I use, I just got two rows instead of 4 rows. Yes, I did not use suppression, but can not get percent and column percent.. please help!

That sounds like you may have a modified template some where.

Run this and show us what appears in the Log:

ods path show;

Re: proc freq crosstab

xiaopingnie — Thu, 24 Jun 2021 13:25:51 GMT

4 proc freq data=analysis.seatbelt_region;
5 table Region*SeatBelt_use;
6 run;

NOTE: Writing HTML Body file: sashtml.htm
WARNING: Format pctfmt. failed to load!
NOTE: There were 7121 observations read from the data set ANALYSIS.SEATBELT_REGION.
NOTE: PROCEDURE FREQ used (Total process time):
real time 0.67 seconds
cpu time 0.09 seconds

7 ods path show;
Current ODS PATH list is:

1. SASUSER.TEMPLAT(UPDATE)
2. SASHELP.TMPLMST(READ)

Re: proc freq crosstab

Cynthia_sas — Thu, 24 Jun 2021 15:56:10 GMT

Hi:

To make sure that ONLY the default PROC FREQ template for crosstabs is being used, try running this code. It will ONLY reference the templates in the production template store that is delivered with SAS:


ods path sashelp.tmplmst(read);
title '1) use only default templates (do NOT use SASUSER.TEMPLAT)';
proc FREQ data = sashelp.cars;
  WHERE make in ('Audi','BMW');
  tables make*type; 
run;

As shown in the TITLE statement, the ODS PATH statement before the PROC FREQ will reset the search path for templates to the default SASHELP.TMPLMST and will bypass the use of SASUSER.TEMPLAT in case the PROC FREQ template was accidentally modified.

Then, if the above code still produces the error, that might mean that someone accidentally changed the PROC FREQ template in the SASHELP template store. That is very unfortunate, if that is the case. You might need to work with Tech Support to get a "good" copy of SASHELP.TEMPMST restored in your session.

Cynthia

Re: proc freq crosstab

xiaopingnie — Thu, 24 Jun 2021 19:28:26 GMT

That works! But I need to add this line of code each time I start SAS program in order to make proc freq crosstab working normally. Anyway to avoid this? Also I am curious that how this happened as I am the only user since the software installed on my computer. I am certainly not that kind of seasoned programmer who knows how to change the PROC FREQ template.

Re: proc freq crosstab

Cynthia_sas — Thu, 24 Jun 2021 19:37:40 GMT

Hi:
. Well, the only way this might have happened would be if you explicitly ran PROC TEMPLATE code to update the template used by PROC FREQ. I suppose it is possible that you are sharing SASUSER.TEMPLAT with others at your company, but that would be out of the ordinary way of setting up SASUSER.TEMPLAT.
Unfortunately, the only way around using the ODS PATH statement every time is finding out how SASUSER.TEMPLAT got accidentally updated. I mean, you could just search for and then delete the SASUSER.TEMPLAT item store or delete the templates out of it. It seems odd that SASUSER.TEMPLAT got updated without your knowledge....but to avoid using the ODS PATH statement, you'll have to get to the bottom of that mystery.
Cynthia

Re: proc freq crosstab

xiaopingnie — Thu, 24 Jun 2021 19:48:56 GMT

Got it. Thank you so much!