topic Re: how to get/link new variables in SAS from 2 Excel sheets in SAS Programming

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

lalohg — Tue, 05 Jun 2018 18:49:19 GMT

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.

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

Kurt_Bremser — Tue, 05 Jun 2018 18:54:18 GMT

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

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

lalohg — Tue, 05 Jun 2018 18:58:38 GMT

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.

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

Kurt_Bremser — Tue, 05 Jun 2018 19:04:10 GMT

Post SAS datasets as data steps, see my footnotes.

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

lalohg — Tue, 05 Jun 2018 19:13:21 GMT

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

merging two excel seets to create new variables in SAS

lalohg — Wed, 06 Jun 2018 11:45:29 GMT

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.

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

Vince_SAS — Wed, 06 Jun 2018 19:37:50 GMT

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

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

lalohg — Wed, 06 Jun 2018 19:10:11 GMT

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

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

Vince_SAS — Wed, 06 Jun 2018 19:35:42 GMT

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

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

lalohg — Thu, 07 Jun 2018 12:53:47 GMT

Hi Vince,

thanks for you answer, what do you suggest?

thanks

Lalo

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

Vince_SAS — Thu, 07 Jun 2018 13:07:40 GMT

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

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

lalohg — Thu, 07 Jun 2018 13:13:49 GMT

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

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

Kurt_Bremser — Thu, 07 Jun 2018 13:17:55 GMT

@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.

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

lalohg — Thu, 07 Jun 2018 15:36:19 GMT

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

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

Vince_SAS — Thu, 07 Jun 2018 15:42:21 GMT

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

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

lalohg — Thu, 07 Jun 2018 15:48:23 GMT

When I run the last part:

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;

the work.sheet1 has the original variables without the disease names, I only see the names of diseases when running the "proc print data=work.sheet1 label"

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

Vince_SAS — Thu, 07 Jun 2018 16:08:20 GMT

The LABEL option only affects the display of column labels (headings). It has no effect on the data values.

Try this code to see if it gives you what you need:

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;

*  Create a new table with separate columns for the diseases;

data work.Sheet1_New;
set work.Sheet1;
length disease1-disease6 $256;
disease1 = put(cause1, $disease.);
disease2 = put(cause2, $disease.);
disease3 = put(cause3, $disease.);
disease4 = put(cause4, $disease.);
disease5 = put(cause5, $disease.);
disease6 = put(cause6, $disease.);
run;

*  Display the data;

title 'Labels NOT used in column headings';

proc print data=work.Sheet1_New;
  label disease1 = 'Cancer name 1'
        disease2 = 'Cancer name 2'
        disease3 = 'Cancer name 3'
        disease4 = 'Cancer name 4'
        disease5 = 'Cancer name 5'
        disease6 = 'Cancer name 6';
run; quit;

title 'Labels used in column headings';

proc print data=work.Sheet1_New label;
  label disease1 = 'Cancer name 1'
        disease2 = 'Cancer name 2'
        disease3 = 'Cancer name 3'
        disease4 = 'Cancer name 4'
        disease5 = 'Cancer name 5'
        disease6 = 'Cancer name 6';
run; quit;

Vince DelGobbo

SAS R&D

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

lalohg — Thu, 07 Jun 2018 16:27:28 GMT

sorry, I got another errors

69 disease1 = put(cause1, $disease.);
---------
48
WARNING: Variable cause1 has already been defined as numeric.
70 disease2 = put(cause2, $disease.);
---------
48
WARNING: Variable cause2 has already been defined as numeric.
71 disease3 = put(cause3, $disease.);
---------
48
WARNING: Variable cause3 has already been defined as numeric.
72 disease4 = put(cause4, $disease.);
---------
48
WARNING: Variable cause4 has already been defined as numeric.
73 disease5 = put(cause5, $disease.);

---------
48
WARNING: Variable cause5 has already been defined as numeric.
74 disease6 = put(cause6, $disease.);

WARNING: Variable cause6 has already been defined as numeric.

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

Vince_SAS — Thu, 07 Jun 2018 16:32:56 GMT

I don't know what the problem is.

I just copied and pasted the complete SAS code into a new SAS session, and it worked without error.

Vince DelGobbo

SAS R&D

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

lalohg — Thu, 07 Jun 2018 17:06:33 GMT

Hi Vince

My mistake, I got mixed up with the variable names. Your code ran just fine!!

Thank you very much for all your help