proc freq crosstab

[xiaopingnie]

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

[data_null__]

---

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

[Cynthia_sas]

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

[xiaopingnie]

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!

[ballardw]

---

@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;

[xiaopingnie]

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)

[ballardw]

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.

[Cynthia_sas]

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

[xiaopingnie]

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.

[Cynthia_sas · Accepted solution]

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

[xiaopingnie]

Got it. Thank you so much!