CODEWITHSUNDEEP
sasformatting
Qimeng Zhang
Qimeng Zhang

Reputation: 31

how to add others in formats in sas

I am trying to use a format table to do look-ups for ICD codes.

I am import a csv format table with two columns icd_code and cancer_type and my main dataset has two columns patient_id and icd.

So I can use the formats to lookup the icd codes and return cancer type in CANCER column. But when some icd codes that are not in the format table the CANCER column will just return the ICD value itself. So I want a 'other = *' in the VALUE of PROC FORMAT. Since I have so more than 200 different cancer types so it would not be possible to add them one by one by VALUE in PROC FORMAT. Is there a way to do that when the ICD code is not in the format table it won't be able to return it own value?

Thank you for helping!

DATA FORMAT; SET FORMAT_TABLE(RENAME = (ICD_CODE = START CANCER_TYPE = LABEL));
RETAIN FMTNAME '$CANCER_TYPE';
RUN;

PROC FORMAT LIB = LIBRARY
    CNTLIN = CAL.FMT;
RUN;

DATA LOOKUP_DATA; SET DATA;
    CANCER = PUT(ICD, $CANCER_TYPE.);
RUN;

Upvotes: 0

Views: 68

Answers (1)

Richard
Richard

Reputation: 27518

The CNTLIN data set will need one additional row to specify the label for other values.

DATA MY_CNTLIN; 
  SET 
    FORMAT_TABLE(RENAME = (ICD_CODE = START CANCER_TYPE = LABEL)) 
    END = LAST_CODE
  ;

  RETAIN FMTNAME '$CANCER_TYPE';

  OUTPUT;
  IF LAST_CODE THEN DO;
    hlo='O';
    label='*';
    OUTPUT;
  END;
RUN;

Read SAS Documentation more details. Proc FORMAT, CNTLIN=, Input Control Data Set

Upvotes: 2

Related Questions