Subsetting identical observations that differ by one variable

[Doyinsola]

Hello guys,

My data looks like the attached screenshot. I have observations that have identical values by some variables but differ by another variable "viralresult". For example, the "order number" and "test type" have the same values but the viral test results are different (see attached screenshot). I'm trying to keep only one observation for each order number and test type, based on the test result variable (e.g. keep one of orderno:1033730189, test: Respiratory Virus Panel PCR that has a positive test result). I am using SAS University Edition (Version: university.cny.sas.com@sas:university-3p.2/3p.2.f23fd5825fb4-1-1)

Hope you can help!

[Tom]

Please post data as text, preferable as a data step that can be used to recreate it.  I cannot copy and paste a photograph of your data into my program editor.

[Doyinsola]

Thank you Tom,

So here are my two different scenarios:

DATA have;
        INPUT orderno  test  $ viralresult $;
        DATALINES;
              1033730189  Respiratory Virus Panel PCR  negative
              1033730189  Respiratory Virus Panel PCR  positive
              1126522178  Respiratory Virus Panel PCR  inconclusive
              1126522178  Respiratory Virus Panel PCR  negative
              ;
Run;

1) If the test results are both negative and positive then I want to keep the positive one.

2) If the results are both inconclusive and negative, I want to keep the inconclusive one.

[Jagadishkatam]

Consider that the data is sorted by orderno test viralresult. Then i use

data want;

set have;

by orderno test viralresult;

if last.test;

run;

[Doyinsola]

Thank you Jagadishkatam,

So here are my two different scenarios:

DATA have;
        INPUT orderno test $ viralresult $;
        DATALINES;
              1033730189  Respiratory Virus Panel PCR  negative
              1033730189  Respiratory Virus Panel PCR  positive
              1126522178  Respiratory Virus Panel PCR  inconclusive
              1126522178  Respiratory Virus Panel PCR  negative
              ;
Run;

1) If the test results are both negative and positive then I want to keep the positive one.

2) If the results are both inconclusive and negative, I want to keep the inconclusive one.

Based on your suggestion I would only be able to retain the positive observation in scenario 1 and the negative observation in scenario 2 right?

[Shmuel]

If your preffered order of viralresult, low to high, has just those 3 options:

     inconclusive - negative - positive

then, that is same order as the alphanumeric sort:

     proc sort data=have; by orderno test viralresult;

     data want; set have; by orderno test; if last.test; run;

if there are more result options then you need assign a prefference code to each viralresult

and select by that code.

[Jagadishkatam]

I also wanted to check what if the test result is inconclusive and positive. As per my assumption you will consider the positive record. But would like to check.

consider that your order of preference is positive followed by inconclusive and last negative. In this case you need to derive a numeric code with the preferred order with proc format like below then I sort the data by code and use first.test to get the expected result

proc format;

invalue $ord

'positive'=1

'inconclusive'=2

'negative'=3;

run;

data want;

set have;

code=input(viralresult,$ord.);

run;

proc sort data=want;

by orderno test code;

run;

data want;

set have;

by orderno test code;

if first.test;

run;

[Doyinsola]

Yes, that is typically the order of preference but for some reasone I can't get the code to work. 

This portion of the code gives the following error:

Data have;
	Set want;
	Code = input (viralresult, best.);
Run;

 NOTE: Invalid argument to function INPUT at line 58 column 9.

 I circumvented this and assign the preferred numeric code a different way. Following this, I ran the next portion of the code (using If first.test;) and obtained an empty dataset with no observations.

[Doyinsola]

This would work, but for the fact that I prefer "positive" over "inconclusive" so it will no longer follow the natural alphanumeric ordering.

[Jagadishkatam · Accepted solution]

sorry but you are supposed to use the informat $ord. and create the code variable and then sort the data by code like below

proc format;
invalue $ord
'positive'=1
'inconclusive'=2
'negative'=3;
run;


data want;
set have;
code=input(viralresult,$ord.);
run;

proc sort data=want;
by orderno test code;
run;


data want;
set have;
by orderno test code;
if first.test;
run;

[Doyinsola]

Thanks Jag,

The final portion of the code below returns an empty dataset though.

Data want;
	Set have;
	If first.test;
Run;

[Jagadishkatam]

yes it will return empty dataset, when you are using the first. then you are supposed to use the by statement, but you did not mention the by statement.

please try this code

proc sort data=want;
by orderno test code;
run;

data want;
set have;
by orderno test code;
if first.test;
run;

[Doyinsola]

Perfect! Thank you for your help, it worked just fine.