Combining categories in SAS

[beatricedesroch]

Hi, 

I have a variable (CRCLSCOD) that have 54 categories. I want to regroup the categories as follow:

Good : A A2 A3 AA B B2 BA

Medium : C C2 C5 CA CC CY D D2 D4 D5 DA

Bad: E E2 E4 EA EC EF EM G GA GY H I IF J JF K L M O P1 S TP U U1 V V1 W Y Z Z1 Z2 Z4 Z5 ZA ZF ZY

Data dm.churn;
run;

Proc format;
Value CRCLSCOD;
if CRCLSCOD="A" or CRCLSCOD="A2" or CRCLSCOD="A3" or CRCLSCOD="AA" or CRCLSCOD="B" or CRCLSCOD="B2" or CRCLSCOD="BA" THEN Good=1;
IF CRCLSCOD="C" or CRCLSCOD="C2" or CRCLSCOD="C5" or CRCLSCOD="CA" or CRCLSCOD="CC" or CRCLSCOD="CY" or CRCLSCOD="D" or CRCLSCOD="D2" or CRCLSCOD="D4" or CRCLSCOD="D5" or CRCLSCOD="DA" THEN Medium=1;
IF CRCLSCOD="E" or CRCLSCOD="E2" or CRCLSCOD="E4" or CRCLSCOD="EA" or CRCLSCOD="EC" or CRCLSCOD="EF" or CRCLSCOD="EM" or CRCLSCOD="G" or CRCLSCOD="GA" or CRCLSCOD="GY" or CRCLSCOD="H" or CRCLSCOD="I" or CRCLSCOD="IF" or CRCLSCOD="J" or CRCLSCOD="JF" or CRCLSCOD="K" or CRCLSCOD="L" or CRCLSCOD="M" or CRCLSCOD="O" or CRCLSCOD="P1" or CRCLSCOD="S" or CRCLSCOD="TP" or CRCLSCOD="U" or CRCLSCOD="U1" or CRCLSCOD="V" or CRCLSCOD="V1" or CRCLSCOD="W" or CRCLSCOD="Y" or CRCLSCOD="Z" or CRCLSCOD="Z1" or CRCLSCOD="Z2" or CRCLSCOD="Z4" or CRCLSCOD="Z5" or CRCLSCOD="ZA" or CRCLSCOD="ZF" or CRCLSCOD="ZY" THEN Bad=1;
run;

It's not working. It says "Statement is not valid or it is used out of proper order"

Can someone help me please ?

[PaigeMiller]

You can't use IF statements in PROC FORMAT. The proper syntax is shown here.

A better approach (maybe):

data want;
    set have;
    length group $ 6;
    if CRCLSCOD=:'A' or CRCLSCOD=:'B' then group='GOOD';
    else if CRCLSCOD=:'C' or CRCLSCOD=:'D' then group='Medium';
    else group='Bad';
run;

[PaigeMiller]

Alternatively

proc format;
    value codef 'A','A2','A3','AA','B','B2','BA' ='Good'
             'C','C2','C5','CA','CC','CY','D','D2','D4','D5','DA' ='Medium'
              other='Bad';
run;

[Tom]

Define the format.

Then use it with the variable.

proc format ;
value $GMB
 'A','A2','A3','AA','B','B2','BA' = 'Good'
 'C','C2','C5','CA','CC','CY','D','D2','D4','D5','DA' = 'Medium'
 'E','E2','E4','EA','EC','EF','EM','G','GA','GY'
,'H','I','IF','J','JF','K','L','M','O','P1','S'
,'TP','U','U1','V','V1','W','Y','Z','Z1'
,'Z2','Z4','Z5','ZA','ZF','ZY' = 'Bad'
 other = 'Unknown'
;
run;

proc freq data=dm.churn ;
  tables CRCLSCOD ;
  format CRCLSCOD $GMB. ;
run;