how to get/link new variables in SAS from 2 Excel sheets

[lalohg]

Hello there,

I have a big data set (few rows as an example in Sheet1 of the attached Excel file) with patients and 6 causes of diseases (columns A to H in sheet1) coded according to the ICD-10 codes (Sheet2). I would like to get the name of the disease but only for cancer (ICD-10 codes from C00 to C97) on separated columns (columns J to O in sheet1) by linking both sheets.

Could you please help me with the SAS code to get columns J to 0?

Thank you

Lalo.

[Kurt_Bremser]

The proper programming interface for MS Office is Visual Basic. If you have problems with that, you should ask in a Microsoft-oriented forum.

[lalohg]

Thanks Kurt, but I would like to it in SAS, instead of the Excel file suposse I already imported it from SAS the two Excel Sheets on separated SAS files.

[Kurt_Bremser]

Post SAS datasets as data steps, see my footnotes.

[lalohg]

thanks anyway..... I'll see what I can do

[lalohg]

Hello there,

I have a big data set (few rows are shown as an example in Sheet1 of the attached Excel file) with patients and 6 causes of diseases (columns A to H in sheet1) coded according to the ICD-10 codes (Sheet2). I would like to get the name of the disease but only for cancer (ICD-10 codes from C00 to C97) on separated columns (columns J to O in sheet1) by linking both sheets.

Could you please help me with the SAS code to get columns J to 0?

Thank you

Lalo.

[Vince_SAS]

Using a SAS format may give you the results that you want.

This code assumes that you have the data in SAS tables named WORK.SHEET1 (patient data) and WORK.SHEET2 (ICD-10 data).

options validvarname=any validmemname=extend nodate nonumber ls=max;

*  Create the CNTLIN table for use with PROC FORMAT;

data work.disease_cntlin;
set work.Sheet2;
length fmtname $7 type $1 start end $3 label $256;
drop 'ICD-10 Code'n 'Disease name'n;

*  Specify the required information for the format;

fmtname = 'DISEASE';
type    = 'C';
start   = 'ICD-10 Code'n;
end     = 'ICD-10 Code'n;
label   = 'Disease name'n;
run;

*  Create the DISEASE format;

proc format cntlin=work.disease_cntlin; run; quit;

*  Display the data using the DISEASE format;

proc print data=work.Sheet1 label;
  format cause1--cause6 $disease.;
  label cause1 = 'Cancer name 1'
        cause2 = 'Cancer name 2'
        cause3 = 'Cancer name 3'
        cause4 = 'Cancer name 4'
        cause5 = 'Cancer name 5'
        cause6 = 'Cancer name 6';
run; quit;

Vince DelGobbo

SAS R&D

[lalohg]

Thanks Vince,

I ran your code and got the following error messages in the Log Window

309 start = 'ICD-10 Code'n ;
ERROR: The name ICD-10 Code is not a valid SAS name.

313 * Create the DISEASE format;
314 proc format cntlin=disease_cntlin;
ERROR: For format $DISEASE, this range is repeated, or values overlap: ICD-ICD.
314! run;

317 format cause1--cause7 $disease.;
ERROR: formato $DISEASE no se he encontrado o no se puede cargar.

wonder what went wrong?

all your help is very much appreciated

Thanks

Eduardo

[Vince_SAS]

My apology; it was a copy-and-paste error.

I updated the SAS code to include the VALIDVARNAME and VALIDMEMNAME options.  Unfortunately the comment is flowing up to the same line as the options, and I can't seem to get it to appear on its own line again.

Vince DelGobbo

SAS R&D

[lalohg]

Hi Vince,

thanks for you answer, what do you suggest?

thanks

Lalo

[Vince_SAS]

Did the code that I provided give you the results that you need?  Attached is an Excel workbook showing the results.

Vince DelGobbo

SAS R&D

[lalohg]

No, I get the error messages that I mentioned. You mentioned "include the VALIDVARNAME and VALIDMEMNAME options" where should I include them in your original code?

Thanks

[Kurt_Bremser]

---

@lalohg wrote:

No, I get the error messages that I mentioned. You mentioned "include the VALIDVARNAME and VALIDMEMNAME options" where should I include them in your original code?

Thanks

---

@Vince_SAS edited his post. Just copy the code from there.

[lalohg]

Hi Vince,

I ran the code but more than replacing the codes with the disease name, I wanted to create new variables but that's fine. One last question, how can I save the proc print output log into a SAS table when running the proc print procedure? 

Thanks

[Vince_SAS]

I don't understand what you mean by this:

@lalohg wrote:

save the proc print output log into a SAS table 

Can you explain that?

Vince DelGobbo

SAS R&D