topic coding error: how to create a list of values by using sql into: properly in SAS Programming

coding error: how to create a list of values by using sql into: properly

Crystal_F — Tue, 30 Jan 2018 22:11:12 GMT

Hello everyone,

I have a problem creating a macro variable made up of values in different length needed.

Here I tried to store the list of icd10 code corresponding to a certain value of another variable called ccs10Cat.

proc sql noprint;
      select distinct quote(compress(icd10Code)) format $7., "CCS10_&ccsValue."
         into  :icd10list separated by ",", 
               :ccs10Var
	        from Ccs.Icd10ccsmapping
		       where compress(ccs10Cat) in ("&ccsValue.");
   quit;

   %put &icd10list;
   %put &ccs10Var;

Ideally, i could save the icd10 codes like 'I8000', 'I8001', .....'I80201', 'I80202'........ 'Z86718', 'Z8672' into the list for a value of the corresponding ccs equal to 118.

In general, an icd10 code could be up to 7 characters in length. But here the maximum that i saved is 5. As you can see the value like "I8012" was supposed to be 'I80201'.

SYMBOLGEN: Macro variable ICD10LIST resolves to
"I8000","I8001","I8002","I8003","I8010","I8011","I8012","I8013","I8020","I8021","I8022","I8023","I8029","I803","I80
8","I809","I81","I820","I821","I8221","I8222","I8229","I823","I8240","I8241","I8242","I8243","I8244","I8249","I824Y
","I824Z","I8250","I8251","I8252","I8253","I8254","I8259","I825Y","I825Z","I8260","I8261","I8262","I8270","I8271","
I8272","I8281","I8289","I8290","I8291","I82A1","I82A2","I82B1","I82B2","I82C1","I82C2","Z8671","Z8672"

I'm hoping some experts here could help me fix my errors and your inputs will be highly appreciated. Thank you so much!

Re: coding error: how to create a list of values by using sql into: properly

Astounding — Tue, 30 Jan 2018 22:16:26 GMT

I'm not sure how the rules of formatting apply here, but it looks like you need to expand your format to $9. instead of $7. It looks like SAS thinks the quotes are part of the formatted length.

Re: coding error: how to create a list of values by using sql into: properly

ballardw — Tue, 30 Jan 2018 23:23:03 GMT

How is that macro variable going to be used? With both quotes and commas I foresee some potential issues passing it around in some code.

Re: coding error: how to create a list of values by using sql into: properly

Crystal_F — Wed, 31 Jan 2018 15:23:36 GMT

Hi Astounding,

As always, thank you so much for your help! It fixed my problem. Now the code works.

Re: coding error: how to create a list of values by using sql into: properly

Crystal_F — Wed, 31 Jan 2018 15:35:03 GMT

Hi Ballardw,

Thank you for bringing this issue to my attention. May i ask what potential issue that you think of? I use the icd10list created to capture diagnosis for Phlebitis as inpatient complication from the claim data. Once a diagnosis code of a claim is found in the list and other criterion are met, the patient will be defined as having Phlebitis as inpatient complication. The relevant code is like:

         else if version[s] eq '0' and dx[s] in (&icd10list) and present[s] ne 'Y' then &ccs10Var. =1;

Thank you for sharing your thoughts!

Re: coding error: how to create a list of values by using sql into: properly

ballardw — Wed, 31 Jan 2018 16:17:07 GMT

@Crystal_F wrote:

Hi Ballardw,

Thank you for bringing this issue to my attention. May i ask what potential issue that you think of? I use the icd10list created to capture diagnosis for Phlebitis as inpatient complication from the claim data. Once a diagnosis code of a claim is found in the list and other criterion are met, the patient will be defined as having Phlebitis as inpatient complication. The relevant code is like:
         else if version[s] eq '0' and dx[s] in (&icd10list) and present[s] ne 'Y' then &ccs10Var. =1;
Thank you for sharing your thoughts!

Sometime a macro value is passed as a parameter to another macro such as :

%somespecialtask (&icd10list);

Since you have commas in the macro variable each one in the above line would be treated as a delimiter between macro parameters. If the macro expects a single parameter that generates errors of "more positional parameters found then defined".

Next is extracting pieces from the list and then doing comparisons:

%let thiscode = %scan(&icd10list,1); for example thinking that you will get the first code.

1    %let icd10list = "I8000","I8001","I8002";
2    %let thiscode = %scan(&icd10list,1);
ERROR: A character operand was found in the %EVAL function or %IF condition where a numeric
       operand is required. The condition was: "I8001"
ERROR: Argument 2 to macro function %SCAN is not a number.

The error is because of the comma the second code is treated as the second parameter of the %scan call.

Quotes may be more of an issue when concatenating your macro variable with other values or use with some macro functions that will treat the " as part of the value.

If the ONLY use is as done with the IN comparison in a data step (issues may arise with the macro IN depending on the value compared) it may not be a problem.

If my only use was in code such as: dx[s] in (&icd10list) I would be very tempted to create a custom format (or several as needed) that would yield a value such a 'Valid' or similar and change the code to

put(dx[s],myicdfmt.) = 'Valid'

Or an informat that yields 1 for found and 0 otherwise: input(dx[s],myicdinfmt.) . Then the value is true(numeric 1)

One side effect is if I have to debug this macro with symbolgen and mprint then I do not get multiple lines of codes and the program flow is a tad easier

Re: coding error: how to create a list of values by using sql into: properly

Crystal_F — Wed, 31 Jan 2018 20:11:14 GMT

Hi Ballardw,

Thank you for sharing your insights. That's very helpful!

And yes, I only use the macro with the in comparison in a data step to avoid hard typing. But I see your point here. Could you please explain how you format 'myicdfmt.' and use it to capture the target diagnosis/condition. The way you use the put function here is new to me and I'd like to know in more details.

Thank you for your help!