DATA Step, Macro, Functions and more

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

Accepted Solution Solved
Reply
Contributor
Posts: 53
Accepted Solution

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

[ Edited ]

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.


Accepted Solutions
Solution
2 weeks ago
SAS Super FREQ
Posts: 353

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

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

View solution in original post


All Replies
Super User
Posts: 9,925

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

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

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Contributor
Posts: 53

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

Posted in reply to KurtBremser

Smiley Sad 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.

Super User
Posts: 9,925

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

Post SAS datasets as data steps, see my footnotes.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Contributor
Posts: 53

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

Posted in reply to KurtBremser

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

Contributor
Posts: 53

merging two excel seets to create new variables in SAS

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.

SAS Super FREQ
Posts: 353

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

[ Edited ]

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

Contributor
Posts: 53

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

Posted in reply to Vince_SAS

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

SAS Super FREQ
Posts: 353

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

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

Contributor
Posts: 53

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

Posted in reply to Vince_SAS

Hi Vince,

thanks for you answer, what do you suggest?

 

 

thanks

Lalo

SAS Super FREQ
Posts: 353

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

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

Contributor
Posts: 53

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

Posted in reply to Vince_SAS

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

Super User
Posts: 9,925

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


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

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Contributor
Posts: 53

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

Posted in reply to Vince_SAS

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

SAS Super FREQ
Posts: 353

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

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

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 24 replies
  • 214 views
  • 9 likes
  • 3 in conversation