topic Replace Set-Merge loop with hash table lookup in SAS Procedures

Replace Set-Merge loop with hash table lookup

Jse — Tue, 06 Jan 2015 18:19:23 GMT

Replace Set-Merge loop with hash table lookup

Hello,

Big time SAS newbie here. I am working with some inherited code and trying to update it.

I have a table with 14 columns for diagnosis (DIAG1-14). I need to compare the values for diagnosis 1 through 14 to another table containing "avoidable" ER diagnoses for matches.

The current program loops 14 times matching one column at a time with a lookup table:

/*TEST DIAG1*/

DATA D1;

SET QIP.DHCS_AER;

AF1=1;

KEEP DIAG1 AF1;

RUN;

PROC SORT; BY DIAG1; RUN;

DATA PASS1;

MERGE ED2 (IN=IN1) D1 (IN=IN2);

BY DIAG1; IF IN1;

RUN;

...and so on up to DIAG14

After the 14 passes a record is flagged as 'avoidable' if diagnoses only found in the lookup table are found in DIAG1-14 (if any other diagnosis is found it is marked 'unavoidable'):

DATA AVOID1;

SET PASS14;

IF DIAG1 NE ' ' AND AF1=. THEN AF1=2;

IF DIAG2 NE ' ' AND AF2=. THEN AF2=2;

IF DIAG3 NE ' ' AND AF3=. THEN AF3=2;

IF DIAG4 NE ' ' AND AF4=. THEN AF4=2;

IF DIAG5 NE ' ' AND AF5=. THEN AF5=2;

IF DIAG6 NE ' ' AND AF6=. THEN AF6=2;

IF DIAG7 NE ' ' AND AF7=. THEN AF7=2;

IF DIAG8 NE ' ' AND AF8=. THEN AF8=2;

IF DIAG9 NE ' ' AND AF9=. THEN AF9=2;

IF DIAG10 NE ' ' AND AF10=. THEN AF10=2;

IF DIAG11 NE ' ' AND AF11=. THEN AF11=2;

IF DIAG12 NE ' ' AND AF12=. THEN AF12=2;

IF DIAG13 NE ' ' AND AF13=. THEN AF13=2;

IF DIAG14 NE ' ' AND AF14=. THEN AF14=2;

LENGTH GROUP $15.;

IF AF1=2 OR AF2=2 OR AF3=2 OR AF4=2 OR AF5=2 OR AF6=2 OR AF7=2 OR AF8=2 OR AF9=2 OR AF10=2 OR

AF11=2 OR AF12=2 OR AF13=2 OR AF14=2 OR AF15=2 THEN

GROUP='UNAVOIDABLE'; ELSE

GROUP='AVOIDABLE';

FORMAT ADMITDT DATE7.;

RUN;

My question is: Would using DCL HASH be a way to improve this loop?

If so, is there any code or white papers anyone would recommend I look at? Any start on the structure of the HASH statement would be greatly appreciated as well.

Thank you very much.

Jesse

Re: Replace Set-Merge loop with hash table lookup

Reeza — Tue, 06 Jan 2015 18:30:07 GMT

Search both Lexjansen.com and sascommunity.org about analysis with diagnosis code(s), as well as here. It's a well documented subject.

Re: Replace Set-Merge loop with hash table lookup

KachiM — Wed, 07 Jan 2015 11:15:05 GMT

Let ED2 contain SASHELP.CLASS. Let your Diagnosis be NAME. Another Data set, D1, has SEVEN NAMES instead of 14 diagnosis. The purpose is LOOKUP D1 for every NAME of ED2 and

make the GROUP as you wanted.

data ed2;

set sashelp.class;

run;

data d1;

input Name $8.;

datalines;

Barbara

James

Jane

Joyce

Louise

Robert

Ronald

;

run;

data want;

if _n_ = 1 then do;

if 0 then set d1;

declare hash h(dataset:'d1', hashexp:20);

h.definekey('Name');

h.definedone();

end;

set ed2;

Group = 'Unavoidable';

if h.find() = 0 then Group = 'Avoidable';

run;

Re: Replace Set-Merge loop with hash table lookup

user24feb — Wed, 07 Jan 2015 13:04:20 GMT

If you google search "sas hash sugi", you should find easily what you are looking for. I haven't tested this code. If your lookup is actually a lookup with only 1 value to join, then try this.

DATA D1;
SET QIP.DHCS_AER;
AF1=1;
KEEP DIAG1 AF1;
RUN;

* etc. - until you#ve defined your 14 data sets, maybe use call execute or so to create code dynamically. no sorting required;

Data Hash (Drop=rc:);
Length Group $15.;
Array rc{*} rc1-rc14;
Set ED2;
If _N_ =1 Then Do;
    Declare Hash H1 (Dataset:'D1');
    H1.Definekey('Diag1');
    H1.Definedata(All:'y');
    H1.Definedone();

    Declare Hash H2 (Dataset:'D2');
    H2.Definekey('Diag2');
    H2.Definedata(All:'y');
    H2.Definedone();

* etc.;
End;

rc1=H1.Find();
rc2=H2.Find();
* etc.;

If Sum(of rc{*}) gt 0 Then Group='UNAVOIDABLE';
Else Group='AVOIDABLE';
Run;