topic Re: Combining tables with character and numeric columns in SAS Programming

Combining tables with character and numeric columns

ammarhm — Fri, 28 Dec 2018 03:47:09 GMT

Hi everyone,

I am trying to combine multiple tables (imported from excel) each of which contains ICD codes. Each table contains 2 columns: ICD_Code_column and ICD_Text_Column

As you might know, the ICD 7, 8 and 9 codes are numerical, the ICD10 are character+numeric= character.

So using a command like this causes an error:

Data ICD;
set ICD6 ICD7 ICD8 ICD9;
run;

The error being that the column containing the ICD code (ICD_Code_column ) is defined both as numeric (in ICD7, ICD8, ICD9) and character (ICD10).

One way is to convert the numeric to character

data ICD7_character;
set ICD7(rename=(ICD_Code_column=x ));
length ICD_Code_column $6 ;
ICD_Code_column= put(x, 6.);
drop x;
run;

data ICD8_character;
set ICD8(rename=(ICD_Code_column=x ));
length ICD_Code_column $6 ;
ICD_Code_column= put(x, 6.);
drop x;
run;

data ICD9_character;
set ICD9(rename=(ICD_Code_column=x ));
length ICD_Code_column $6 ;
ICD_Code_column= put(x, 6.);
drop x;
run;

once all that is done then I can do

Data ICD;
set ICD7_character ICD8_character ICD9_character ICD10;
run;

It does work, but does not look like an elegant solution.

Could anyone suggest a grammatically more efficient/ better solution? Using a macro or even a loop in Data - Set to combine all tables?

Thanks

Re: Combining tables with character and numeric columns

Kurt_Bremser — Fri, 28 Dec 2018 08:44:36 GMT

Save the Excel data to csv and import all tables with the same data step, and all your problems will be solved.

Re: Combining tables with character and numeric columns

ammarhm — Mon, 31 Dec 2018 00:16:40 GMT

@Kurt_Bremser

Thanks

The import process works fine. The issue is that some ICD codes (ICD7-9) are numerical and are imported to SAS correctly as numerical while ICD 10 is a string variable and is imported as such\

You cant set the 4 data files directly as variables are of different types, I was more looking into converting them to character variable in a simple way

Re: Combining tables with character and numeric columns

Kurt_Bremser — Mon, 31 Dec 2018 07:17:05 GMT

You need to fix your problem at the earliest stage, which is your import; at the moment it is very obviously NOT working, as it produces inconsistent results.

ICD codes are codes, and as such are always stored as character, as you never do calculations with them, but only use them for grouping.

I repeat: use a sensible data format for the import (NOT Excel), and set correct types in your data step that reads the files.

Re: Combining tables with character and numeric columns

s_lassen — Mon, 31 Dec 2018 09:49:35 GMT

You can do this in a single datastep, e.g.:

data ICD;
  set 
    ICD7(rename=(ICD_Code_column=x ))
    ICD8(rename=(ICD_Code_column=x ))
    ICD9(rename=(ICD_Code_column=x ))
    ICD10(in=ok)
    ;
  if not OK then
    ICD_Code_column= put(x, 6.); 
  drop x; 
run;

Re: Combining tables with character and numeric columns

ballardw — Mon, 31 Dec 2018 17:13:13 GMT

@ammarhm wrote:

Hi everyone,

I am trying to combine multiple tables (imported from excel) each of which contains ICD codes. Each table contains 2 columns: ICD_Code_column and ICD_Text_Column

As you might know, the ICD 7, 8 and 9 codes are numerical, the ICD10 are character+numeric= character.

So using a command like this causes an error:

Actually, since the ICD codes have never been intended to use in arithmetic (what would the meaing of adding ICD codes 001 and 021 for instance) they really should never be treated or considered numeric. The decimal values when used are showing subsets not numeric variation in the codes.

I kind of cringe with the idea of having 4 different coding schemes in a single data set in a single variable.