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

Hi

 

I am using SAS 9.3

 

My objective is to take the inpatient data set, link it to the ED dataset (for those admitted via the Emergency Department) and determine if the same procedure with the same procedure date is coded in both datasets.

 

My original step was to combine the two datasets via proc SQL where the ED dispoistion date = inpatient admit date and that is working fine (though I welcome any feedback on whether this is the correct approach).  So now I have a dataset per chart number with 20 possible procedures (pxcde1-pxcde20) and 10 potential ED procedures (px1-px10).  I also have 20 possible inpatient procedure dates pxstdate1-pxstdate20) and 10 possible ED procedure dates (pxdt1-pxdt10). 

 

What I want to do is compare the inpatient procedure with procedure date to the ED procedures and procedure dates to see if they match.  Below is some test data where registration 002, 004 and 005 should be matched and the other two are not.  I appreciate any and all assistance, thanks..

 

data test_grp;
input @1 chart $5.
	  @6 regn  $3.
	  @9 AdmDate yymmdd8.
      @17 DisDate yymmdd8.
	  @25 D_Px1 $5.
	  @30 D_Px2 $5.
	  @35 PxDt1 yymmdd8.
	  @43 PxDt2 yymmdd8.
	  @51 N_Px1 $5.
	  @56 N_Px2 $5.
	  @61 N_PDt1 yymmdd8.
	  @69 N_PDt2 yymmdd8.
;
format admdate disdate PxDt1-PxDt2 N_PDt1-N_PDt2 yymmdd8.;
cards;
A123400120120329201204023AN201GV5220120330201203303GY201GV522012032920120329
A586700220120429201205051VC743AN2020120429201204293AN20     20120429
A898900320120529201205313OT20     20120529        3OT203AN202012052820120528
A232400420120629201207153GY201GV5220120629201206293GY201GV522012062920120629
A285500520120704201207143OT203AN2020120704201207043OT203AN202012070420120704
run; 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Reeza
Super User
array _proc1(*) <list of variables>;
array _proc2(*) <list of variables>;
array _date1(*) <list of variables>;
array _date1(*) <list of variables>;

flag=0;

do i=1 to dim(_proc1);
    find_index = whichc(_proc1(i), of _proc2(*))
    if find_index > 0 then do;
          *check dates;
            if _date(i) = _date2(find_index) then flag=1;
     end;
end;

View solution in original post

22 REPLIES 22
Reeza
Super User

You'll need a loop. 

 

Create two arrays one for each set. 

Loop through the shorter one and use WHICHC to search for the same code in the other array.

Assuming you've already set up the arrays, using _array1/_array2, here's what your code may look like:

 

flag=0;

do i=1 to dim(_array1);
    if whichc(_array1(i), of _array2(*)) then flag=1;
end;

 

shellp55
Quartz | Level 8

Hi

 

PGStats, thanks for that but it would take a few more  steps to get it into long format ...which i can do but i want to ask a few more questions about Reeza's suggestion.  Reeza, I have two qualifying statements i.e. the procedure AND the procedure date must match between the two so how do I do this with your suggestion?  Thanks.

Reeza
Super User

Create two arrays for your dates as well.

 

If you find the match, the WHICHC will return the index. This means you have two pointers, the i variable and the results from WHICHC. 

Use those to check if the dates match in the arrays. If you can have multiple diagnosis in the same line this may not work...

shellp55
Quartz | Level 8

Sorry Reeza, I'm not understanding your response.  Using my fake data, how would this be done?  Thanks.

PGStats
Opal | Level 21

If your data had a long structure, you could simply do a SQL INTERSECT between both sets.

PG
ChrisNZ
Tourmaline | Level 20

Your data should be organised like this, and then everything becomes simple:

 

Table 1

chart regn AdmDate DisDate
A1234 1 12/03/2029 12/04/2002
A5867 2 12/04/2029 12/05/2005
A8989 3 12/05/2029 12/05/1931
A2324 4 12/06/2029 12/07/2015
A2855 5 12/07/2004 12/07/2014

 

Table 2

chart regn PXD_NO PXD_DT PXD
A1234 1 1 12/03/1930 3AN20
A1234 1 2 12/03/1930 1GV52
A5867 2 1 12/04/2029 3AN20
A5867 2 2 12/04/2029 1VC74

 

Table3

chart regn PXN_NO PXN_DT PXN
A1234 1 1 12/03/2029 3GY20
A1234 1 2 12/03/2029 1GV52
A5867 2 1 12/04/2029 3AN20

 

 

shellp55
Quartz | Level 8

PG, I'm unaware of how to use intersect, can you please provide more information?  Thanks.

PGStats
Opal | Level 21

Rows of a table can be considered as elements of a set. There are three set operations defined in SQL: UNION, EXCEPT and INTERSECT. They are described here:

 

http://support.sas.com/documentation/cdl/en/sqlproc/63043/HTML/default/viewer.htm#p1o6k7t8y56hobn1mu...

PG
shellp55
Quartz | Level 8

Hi

 

Sorry but still struggling with this.  I looked up intersect and because the files have to be the same, this won't allow me to identify the actual case afterwards.  For example, acctno is different on the two datasets because they are two different data types (ED and inpatient).  Unless I'm missing something about intersect? 

 

Thanks.

Reeza
Super User
array _proc1(*) <list of variables>;
array _proc2(*) <list of variables>;
array _date1(*) <list of variables>;
array _date1(*) <list of variables>;

flag=0;

do i=1 to dim(_proc1);
    find_index = whichc(_proc1(i), of _proc2(*))
    if find_index > 0 then do;
          *check dates;
            if _date(i) = _date2(find_index) then flag=1;
     end;
end;
Reeza
Super User
@shellp55 note that I didn't test the code, I'm leaving that to you, but hopefully it gives you the idea.
shellp55
Quartz | Level 8

Thanks Reeza!  Just wondering what the code of

dim(_array1)"

 is supposed to be doing?  It produces an error.

Reeza
Super User

It's the loop control. Since I changed the names of the array, change the _array1 to be _proc1

shellp55
Quartz | Level 8

I am marking this complete (Reeza's code) because it worked for the test dataset provided.  However it isn't working for my real dataset.  I am assuming that the larger array (the DAD has 20 versus ED has 10) should be array1 but it is flagging cases as being valid when no procedure matches. 

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
  • 22 replies
  • 1838 views
  • 0 likes
  • 4 in conversation