Formatting issue

[GS2]

I am using SAS 9.4

I used a format to make 'C' and 'X' both become 'Y'

In my frequency both C and X have become Y but they are still separate. So I now have 2 'Y's with different frequencies when I want 1 'Y' with the frequency of the 2 'Y's put together. If this doesn't make sense I can clarify but I am looking for why this would. I have used the format before on a previous data set and it worked but now for this data set it is not. My type, length, format and informat are the same for both datasets I used this format on.Thank you

proc freq data = working.GI_ED_visits_DP0_5_013119 order=freq;
tables admt_cmplnt_ftdesc;
by chs_site_desc;
format site_desc $site_desc.;
run;

proc format;
VALUE $SITE_DESC                'C','X'     =     'X';
run;

[GS2]

'C','X' = 'Y'

Typo, Sorry!

[Tom]

When you use a format with PROC FREQ it will collapse based on the formatted value, but the actual value it stores will be one of the values in the data.

If you don't want that behavior then use the PUT() function to convert the values.

Might be faster to convert AFTER collapsing with PROC FREQ.

proc format;
value $site_desc
'C','X'     =     'X'
;
run;

data have ;
  input bygrp $ site $ ;
cards;
1 A
1 C
1 X
2 C
3 X
4 A
;

proc freq ;
  by bygrp;
  tables site / out=want noprint;
  format site $site_desc. ;
run;

proc print data=want;
run;
proc print data=want;
  format _all_;
run;

data want2;
  set want ;
  new_site = put(site,$site_desc.);
  format _all_;
run;

proc print;
run;

Obs    bygrp    site    COUNT    PERCENT    new_site

 1       1       A        1       33.333       A
 2       1       C        2       66.667       X
 3       2       C        1      100.000       X
 4       3       X        1      100.000       X
 5       4       A        1      100.000       A

[Cynthia_sas · Accepted solution]

Hi:

  What you're describing is not what I see. When I run a version of your program, using slightly different data, I do see that PROC FREQ collapses both C and X into 1 row for Y in PROC FREQ:

So I'm not sure what's going on with your program or data.

Cynthia