BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
lalohg
Quartz | Level 8

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Vince_SAS
Rhodochrosite | Level 12

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

26 REPLIES 26
lalohg
Quartz | Level 8

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.

lalohg
Quartz | Level 8

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

lalohg
Quartz | Level 8

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
Rhodochrosite | Level 12

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
Quartz | Level 8

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
Rhodochrosite | Level 12

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
Quartz | Level 8

Hi Vince,

thanks for you answer, what do you suggest?

 

 

thanks

Lalo

Vince_SAS
Rhodochrosite | Level 12

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
Quartz | Level 8

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
Super User

@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
Quartz | Level 8

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
Rhodochrosite | Level 12

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 26 replies
  • 1732 views
  • 9 likes
  • 3 in conversation