topic Re: merge, spedis, do loop, or macro in SAS Programming

merge, spedis, do loop, or macro

Bal23 — Tue, 19 Jul 2016 19:23:06 GMT

I have a large dataset, contains values either icd9 , icd10, or something else. I can use merge to a reference dataset to see whether it is icd9 or icd10. But I am thinking whether there are some other ways, using, strip, trim, or substr, index, or spedis function.

If I use spedis function, I can first merge it to icd9 reference list and icd10 reference list. Then I compare the points I get, to judge whether it is icd9 or icd10. Because there are lots of values neither icd9 nor icd10, that needs some additional work, means that I have to merge back to the original large dataset, and the duplicate record is somewhat hard to remove; or at least time consuming

I want to know if

Can I skip merge, but use do loop, or macro, useIf strip(string) = ‘ ‘ ( the value is on another dataset, icd10 code, reference dataset, about 69823 codes) then result =”10”
If strip(string) = ‘ ‘ (but the value is on another dataset, icd9 code, reference dataset, about 14025 codes) then result =”9”
Or any other more efficient way to do, without merge

Thanks

Re: merge, spedis, do loop, or macro

Quentin — Tue, 19 Jul 2016 20:00:33 GMT

You might try loading your lookup tables of ICD9 codes and ICD10 codes into two different hash tables. Then you can use hash table lookups. One benefit of this approach is don't have to sort the data like you would with a merge. If you want to see some sample code, I would suggest you post a small amount of sample data.

Re: merge, spedis, do loop, or macro

Ludwig61 — Tue, 19 Jul 2016 20:03:59 GMT

#3 Use a hash object. Load your ICD9 and ICD10 reference tables into one hash with lookup values of 'ICD9' and 'ICD10' respectively. See if the code value in each row of your data is in the hash and retrieve either the 'ICD9 ' or 'ICD10' from that hit. If you don't get a hit, assign the value 'Other'.

Re: merge, spedis, do loop, or macro

Bal23 — Tue, 19 Jul 2016 20:36:22 GMT

attached as a sample dataset

Re: merge, spedis, do loop, or macro

Bal23 — Tue, 19 Jul 2016 20:38:05 GMT

Sorry I do not know anything ab hash object. I have provided my smalle sample data. Would you please provide sas code? Thanks.

Re: merge, spedis, do loop, or macro

Reeza — Tue, 19 Jul 2016 20:39:50 GMT

Pretty sure I suggested this earlier and I think it's probably the simplest in many ways. Create two formats, you can do that from reference tables, look up CNTLIN. Add in an other for not found formats.

Then the code can be:

if put(code, icd9_fmt.) ne 'Other' then code_type='ICD9';

else if put(code, icd10_fmt.) ne 'Other' then code_type='ICD10';

else code_type='Other';

Re: merge, spedis, do loop, or macro

Quentin — Tue, 19 Jul 2016 20:46:36 GMT

@Bal23, please post a small amout of sample data as text in your question (preferably a data step with a cards statement). I don't trust Excel files (too many viruses / bad data conversions). You could post perhaps three small datasets: 10 records of sample data, 5 records of icd-9 lookups, and 5 records of icd-10 lookups. I think that should be enough for people to work with.

The exact matches should be easy to find (with hash approach or @Reeza's format approach, which may be easier for you if you haven't played with hashes before). If you want fuzzy matching of the sort that can be done with SPEDIS function, that will take more thought.

Re: merge, spedis, do loop, or macro

Bal23 — Tue, 19 Jul 2016 21:18:16 GMT

data have; length id icd date 10; format icd dollar 8.; 
input id date icd; datalines; id date icd 
123 101510 a09
  234 51812 k09 
256 61912 h980 
345 120213 98 
534 12310 overw 
2133 040512 e03
526 32116 underw
 21 10213 e8808
 56 20115 v342 
23   30314 345
41  020215 278
; run;

data icd-10;

input icd10 longdes;

E00 Congenital iodine-deficiency syndrome

E01 Iodine-deficiency related thyroid disorders and allied conditions

E02 Subclinical iodine-deficiency hypothyroidism

E03 Other hypothyroidism

E04 Other nontoxic goiter

E05 Thyrotoxicosis [hyperthyroidism]

E06 Thyroiditis

A09 Infectious gastroenteritis and colitis, unspecified

H99 Other and unspecified disorders of circulatory system

data icd9 longdes;

278 evans sym

0345 inflam

0098 colon dis

v32 hand injury

v54 rib fracture

e33 eye dise

e03 unspecific dis

e03 could be either icd9 or 10, some are neither icd9 nor icd10

Re: merge, spedis, do loop, or macro

Bal23 — Tue, 19 Jul 2016 21:06:11 GMT

sorry Reeza, I do not understand it. I did check cntlin online, but still have no clue on how to use it.

I have provided my sample dataset, as well as two reference dataset, would you please provide more details? Thanks.

Re: merge, spedis, do loop, or macro

error_prone — Tue, 19 Jul 2016 21:19:57 GMT

Using a dataset to create a format is explained in http://www2.sas.com/proceedings/forum2007/068-2007.pdf.

Re: merge, spedis, do loop, or macro

Reeza — Tue, 19 Jul 2016 23:28:25 GMT

@Bal23 wrote:

sorry Reeza, I do not understand it. I did check cntlin online, but still have no clue on how to use it.

I have provided my sample dataset, as well as two reference dataset, would you please provide more details? Thanks.

Where are you checking? I highly recommend searching for papers on lexjansen.com + keywords introduction/beginner which will provide you with a lot of resources.

Re: merge, spedis, do loop, or macro

Quentin — Wed, 20 Jul 2016 12:29:32 GMT

I think @Reeza is right that a PROC FORMAT approach will be easiest. Formats allow simple look-ups like you need. And if you are working with ICD9 codes, you probably already have formats that labels them all (or if you don't, it's worth building the formats).

Below is a small example of using formats to look up each code in HAVE to assign it a label per ICD9, and a label per ICD10. Of course you can add logic to catch codes that have both ICD9 and ICD10 labels, or neither. But it's a starting point.

If you need to build the formats from existing data, then PROC FORMAT CNTLIN is the way to go (not shown below).

data have; 
input id $1. icd : $3.; 
cards;
1 E01
2 E02
3 E03
4 E33
5 E99
; 
run;

proc format;
  value $ICDnine
    "E33"="eye dise"
    "E03"="unspecific dis"
    other=" "
  ;
  value $ICDten
    "E01"="Iodine-deficiency related thyroid disorders and allied conditions"
    "E02"="Subclinical iodine-deficiency hypothyroidism"
    "E03"="Other hypothyroidism"
    other=" "
  ;
run;

data want; 
  set have;
  length ICD9text $100 ICD10text $100;
  ICD9text=put(icd,$ICDnine.);
  ICD10text=put(icd,$ICDten.);
  put (icd icd9text icd10text)(=);
run;

Returns in log:

199  data want;
200    set have;
201    length ICD9text $100 ICD10text $100;
202    ICD9text=put(icd,$ICDnine.);
203    ICD10text=put(icd,$ICDten.);
204    put (icd icd9text icd10text)(=);
205  run;

icd=E01 ICD9text=  ICD10text=Iodine-deficiency related thyroid disorders and allied conditions
icd=E02 ICD9text=  ICD10text=Subclinical iodine-deficiency hypothyroidism
icd=E03 ICD9text=unspecific dis ICD10text=Other hypothyroidism
icd=E33 ICD9text=eye dise ICD10text=
icd=E99 ICD9text=  ICD10text=
NOTE: There were 5 observations read from the data set WORK.HAVE.
NOTE: The data set WORK.WANT has 5 observations and 4 variables.

Re: merge, spedis, do loop, or macro

Bal23 — Wed, 20 Jul 2016 13:52:15 GMT

how to do with hash tables?

Re: merge, spedis, do loop, or macro

Bal23 — Wed, 20 Jul 2016 14:12:09 GMT

thank you

my original dataset contains values like A2.89, K299.47, 034.568,

They do not quite match the reference dataset, which already remove "."

i need some more flexible ways, which means, do I have to modify the value of reference dataset and my have dataset first, then use CNTLIN?

Re: merge, spedis, do loop, or macro

Quentin — Wed, 20 Jul 2016 14:17:02 GMT

Yes, for the PROC FORMAT approach (and hash approach) the values in your HAVE data must match the values defined in the format (or hash table key) exactly. So you could clean the values to make them consistent. Or if you want FUZZY matching, would need a different approach.

Re: merge, spedis, do loop, or macro

ballardw — Wed, 20 Jul 2016 14:29:47 GMT

@Bal23 wrote:

thank you

my original dataset contains values like A2.89, K299.47, 034.568,

They do not quite match the reference dataset, which already remove "."

i need some more flexible ways, which means, do I have to modify the value of reference dataset and my have dataset first, then use CNTLIN?

I do a very small amount of work with ICD codes but your comment about "already remove "."" makes me very concerned as those decimals are very significant and a remember some where it is possibly to have A2.8 and A28. type structures. I would question the decision about creating any type of reference table without the decimals.

And for formats I might also recommend, though it is more work, to investigate MULTILABEL formats. When used with the procedures that support them it makes reports of the form "I want all injuries to feet with a total AND the break out by subcategories of foot injuries as well" easier.

Re: merge, spedis, do loop, or macro

Bal23 — Wed, 20 Jul 2016 14:47:10 GMT

thanks

your are right

for the current project, i only need to tell whether it is icd9 or 10, so A2.8, OR A28.9 are both icd-10, therefore this is not my concern for this

Re: merge, spedis, do loop, or macro

Bal23 — Wed, 20 Jul 2016 14:50:17 GMT

Thanks. I did look at a paper, PROC FORMAT: USE OF THE CNTLIN OPTION, by Karuna

the author explained the duplicate records, So the id SHOULD BE unique in order for the format procedure to succesffully create a format for that variable

for my project, there are duplicate records and I cannot remove, I need to keep them

any advice or more detailed info on this solution?

Re: merge, spedis, do loop, or macro

Bal23 — Wed, 20 Jul 2016 14:51:11 GMT

Thanks. I did look at a paper, PROC FORMAT: USE OF THE CNTLIN OPTION, by Karuna

the author explained the duplicate records, So the id SHOULD BE unique in order for the format procedure to succesffully create a format

for that variable

for my project, there are duplicate records and I cannot remove, I need to keep them

any advice or more detailed info on this solution?

Re: merge, spedis, do loop, or macro

Reeza — Wed, 20 Jul 2016 16:40:48 GMT

@Bal23 wrote:

Thanks. I did look at a paper, PROC FORMAT: USE OF THE CNTLIN OPTION, by Karuna

the author explained the duplicate records, So the id SHOULD BE unique in order for the format procedure to succesffully create a format

for that variable

for my project, there are duplicate records and I cannot remove, I need to keep them

any advice or more detailed info on this solution?

The format is created based on your ICD9/10 reference tables. Each ICD9/10 code is unique. The format is applied to your other table and it doesn't matter how many duplicates.

Perhaps you should try the solution and see if it works rather than guessing all the reasons it may not work.