DATA Step, Macro, Functions and more

Comparing Arrays in same dataset?

Accepted Solution Solved
Reply
Regular Contributor
Posts: 177
Accepted Solution

Comparing Arrays in same dataset?

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; 

 


Accepted Solutions
Solution
‎10-04-2017 01:51 PM
Super User
Posts: 23,963

Re: Comparing Arrays in same dataset?

[ Edited ]
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


All Replies
Super User
Posts: 23,963

Re: Comparing Arrays in same dataset?

[ Edited ]

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;

 

Regular Contributor
Posts: 177

Re: Comparing Arrays in same dataset?

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.

Super User
Posts: 23,963

Re: Comparing Arrays in same dataset?

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...

Regular Contributor
Posts: 177

Re: Comparing Arrays in same dataset?

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

Esteemed Advisor
Posts: 5,616

Re: Comparing Arrays in same dataset?

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

PG
Super User
Posts: 2,499

Re: Comparing Arrays in same dataset?

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

 

 

Regular Contributor
Posts: 177

Re: Comparing Arrays in same dataset?

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

Esteemed Advisor
Posts: 5,616

Re: Comparing Arrays in same dataset?

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
Regular Contributor
Posts: 177

Re: Comparing Arrays in same dataset?

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.

Solution
‎10-04-2017 01:51 PM
Super User
Posts: 23,963

Re: Comparing Arrays in same dataset?

[ Edited ]
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;
Super User
Posts: 23,963

Re: Comparing Arrays in same dataset?

@shellp55 note that I didn't test the code, I'm leaving that to you, but hopefully it gives you the idea.
Regular Contributor
Posts: 177

Re: Comparing Arrays in same dataset?

Thanks Reeza!  Just wondering what the code of

dim(_array1)"

 is supposed to be doing?  It produces an error.

Super User
Posts: 23,963

Re: Comparing Arrays in same dataset?

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

Regular Contributor
Posts: 177

Re: Comparing Arrays in same dataset?

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. 

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 22 replies
  • 390 views
  • 0 likes
  • 4 in conversation