topic Re: Comparing Arrays in same dataset? in SAS Programming

Comparing Arrays in same dataset?

shellp55 — Sun, 01 Oct 2017 19:03:38 GMT

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;

Re: Comparing Arrays in same dataset?

Reeza — Sun, 01 Oct 2017 21:35:21 GMT

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;

Re: Comparing Arrays in same dataset?

PGStats — Mon, 02 Oct 2017 03:35:58 GMT

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

Re: Comparing Arrays in same dataset?

ChrisNZ — Mon, 02 Oct 2017 04:56:36 GMT

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

Re: Comparing Arrays in same dataset?

shellp55 — Mon, 02 Oct 2017 13:14:18 GMT

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.

Re: Comparing Arrays in same dataset?

Reeza — Mon, 02 Oct 2017 14:53:15 GMT

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

Re: Comparing Arrays in same dataset?

shellp55 — Mon, 02 Oct 2017 20:03:16 GMT

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

Re: Comparing Arrays in same dataset?

shellp55 — Mon, 02 Oct 2017 20:04:04 GMT

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

Re: Comparing Arrays in same dataset?

PGStats — Mon, 02 Oct 2017 20:28:07 GMT

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#p1o6k7t8y56hobn1mup90vpf4ye6.htm

Re: Comparing Arrays in same dataset?

shellp55 — Wed, 04 Oct 2017 17:03:50 GMT

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.

Re: Comparing Arrays in same dataset?

Reeza — Wed, 04 Oct 2017 18:19:03 GMT

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;

Re: Comparing Arrays in same dataset?

Reeza — Wed, 04 Oct 2017 17:15:37 GMT

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

Re: Comparing Arrays in same dataset?

shellp55 — Wed, 04 Oct 2017 17:29:15 GMT

Thanks Reeza! Just wondering what the code of

dim(_array1)"

is supposed to be doing? It produces an error.

Re: Comparing Arrays in same dataset?

Reeza — Wed, 04 Oct 2017 17:30:18 GMT

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

Re: Comparing Arrays in same dataset?

shellp55 — Wed, 04 Oct 2017 17:50:49 GMT

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.

Re: Comparing Arrays in same dataset?

Reeza — Wed, 04 Oct 2017 17:56:15 GMT

array1 should be the smaller list.

This doesn't account for missing though so you may want to add a filter for missing to stop the DO loop.

Re: Comparing Arrays in same dataset?

shellp55 — Wed, 04 Oct 2017 18:16:49 GMT

Got it, makes sense because it is matching blank to blank and considers that a match. Sorry for being dense but not sure how to add that filter...but I will search the site. Thanks again for all your help.

Re: Comparing Arrays in same dataset?

Reeza — Wed, 04 Oct 2017 18:20:05 GMT

if missing(_proc1(i)) then leave;

I think you add that into the top of the do loop.

This does assume that once you find a missing value all other values will be missing, which is pretty much how I recall medical data being formatted.

Re: Comparing Arrays in same dataset?

Reeza — Wed, 04 Oct 2017 18:20:25 GMT

It's leave or break...you can test them 😉

Re: Comparing Arrays in same dataset?

shellp55 — Wed, 04 Oct 2017 18:40:06 GMT

Thanks Reeza! I've tried the code in various spots but it still shows as zero cases and I know there are some matching the criteria.

I've used:

do I = 1 to dim(_proc1);
find_index=whichc(_proc1(i) of _proc2(*));
if missing(_proc1(i)) then leave;
else if find_index>0 then do;
if _date1(i)=_date2(find_index) then flag=1;
end;
end;