topic Re: Creating a format from an imported table in SAS Programming

Creating a format from an imported table

beverlyam — Wed, 25 Feb 2026 16:38:05 GMT

I am trying to create a format based on a table that I imported into SAS. The table includes ICD 10 codes as once variable and the corresponding description as the other variable. I am trying to create a format that would call out the code and description as one. I am calling the format is icdalpha.

So far this is what I have come up with but I continue to get errors:

proc import datafile= 'O:\Data Dictionaries\All ICD9_10 Diagnosis Codes Dec 2025.xlsx'
OUT=DX
DBMS=xlsx
REPLACE;SHEET="Formatted";
GETNAMES=YES;
;
RUN;

data dx2;
set dx;

/* Rename columns to match SAS format requirements */
retain fmtname "icdalpha"; /* The name of your future format */
rename CODE = start
SHORT__DESC = label;
run;

data dx2_clean;
set dx2;
/* Remove rows where the code is missing */
if missing(start) then delete;

/* Clean up the values */
start = upcase(strip(start));
label = strip(label);
run;

proc sort data=dx2_clean nodupkey;
by start;
run;

data dx2_final;
set dx2_clean;

/* 1. Remove rows where the code is missing or just a blank string */
if compress(start) in ('', '.') then delete;

/* 2. Ensure everything is Upper Case to avoid 'a1' vs 'A1' conflicts */
start = upcase(strip(start));
run;

/* 3. Remove any remaining duplicates of the same code */
proc sort data=dx2_final nodupkey;
by start;
run;

/* 4. Build the format */
proc format cntlin=dx2_final;
run;

I need help in trying to figure out where I am going wrong. I have attached the table I am using.

Re: Creating a format from an imported table

Kathryn_SAS — Wed, 25 Feb 2026 17:09:07 GMT

It does not look like the file is attached. However, if the Start/Code variable is character, you need to create a character format as follows:

data dx2;
set dx;

/* Rename columns to match SAS format requirements */
retain fmtname "$icdalpha"; /* The name of your future format */
rename CODE = start
SHORT__DESC = label;
run;

If you continue to have problems, please send the log that shows the Error messages you are getting.

Re: Creating a format from an imported table

sbxkoenk — Wed, 25 Feb 2026 17:15:32 GMT

What errors do you get?

Here are some examples:

Cheers,

Koen

Re: Creating a format from an imported table

beverlyam — Wed, 25 Feb 2026 17:17:58 GMT

The table is now attached

Re: Creating a format from an imported table

ballardw — Wed, 25 Feb 2026 17:19:05 GMT

Best practice when code generates errors is to include the LOG with the code and error, warning or other messages of concern. Copy the text from the log, on the forum open a text box window using the </> icon above the main message window and paste the code and messages. The text box is important because the forum software will refomat text pasted into the main message window resulting in some of the diagnostic information that SAS often supplies.

As a minimum your code should also include a TYPE variable to tell SAS whether the valus in the START variable are numeric or character and display or input. A type of N (character value of the variable type) means Start is supposed to be numeric and is a display format, Type of C means start is Character and is a display format, Type of I (upper case i) is informat for numeric values, type of J is informat for character values, Type P means a PICTURE format.

If your data contains "A1" and "a1" for the variable and want them both to display the same label text then you want both values in the START.

Because you IMPORT the data from Excel you may have issues of same value, i.e. "A1" with multiple values of the label cause by capitalization differences or white space in values (different numbers of spaces can be hard to see) or leading spaces. SAS doesn't like that for formats or informats and will show errors of duplicated ranges.

Suggestion: Write a small, as in two or there value/invalue statements as normal proc format code and use the CNTLOUT option to write a data set of those. That will show values for the variables needed in the data set.

You may want to consider adding and Other = error option to the above example trial code if creating an INFORMAT. The Other option, which would go into a variable named HLO, coupled with the error option means that use of the format with unknown or unexpected values tells you that such a value was encountered in the log with an invalid data message.

Re: Creating a format from an imported table

Kathryn_SAS — Wed, 25 Feb 2026 17:23:23 GMT

Your variables are character, so you need to create a character format:

retain fmtname "$icdalpha"; /* The name of your future format */

data dx2;
set dx;

/* Rename columns to match SAS format requirements */
retain fmtname "$icdalpha"; /* The name of your future format */
rename CODE = start
SHORT__DESC = label;
run;

Re: Creating a format from an imported table

beverlyam — Wed, 25 Feb 2026 18:32:10 GMT

Hi thanks for your help it now runs!