BookmarkSubscribeRSS Feed
Jse
Fluorite | Level 6 Jse
Fluorite | Level 6

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

3 REPLIES 3
Reeza
Super User

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

KachiM
Rhodochrosite | Level 12

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;

user24feb
Barite | Level 11

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;

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!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

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
  • 3 replies
  • 898 views
  • 6 likes
  • 4 in conversation