Variable meets 2 criteria

[cort125]

I am working with a large data set and trying to figure out how I can create a code where the specimen number meets 2 criteria: Report Status = "CORRECTIVE" and "FINAL" while also keeping all variables in the table:

This is the dataset I have:

Record ID	Specimen number	Date Collected	Report Status
12345	W2345678	1/5/2020	Corrective  Final
23456	M9876799	3/4/2023	Corrective
84569	N45678987	5/22/2022	Corrective  Final
78766	P73839303	6/9/2022	Final
35473	R37390202	4/7/2023	Corrective

This is what I want:

Record ID	Specimen number	Date Collected	Report Status
12345	W2345678	1/5/2020	Corrective  Final
84569	N45678987	5/22/2022	Corrective  Final

I also want to create a code where the specimen number has a Report Status as Corrective only but was never Final.

Can anyone help me with this? Thank you so much!

[PaigeMiller]

I realize you are new here, but we cannot work with data in this form. This is a good example why many people here (including me) insist on data sets provided as SAS data step code (examples and instructions).

I cannot tell what the first entry under Report Status is. Is it two words separated by a space? Is it two words separated by a carriage return? Is it something else? (And since Report Status is not a valid variable name, I suspect you are not really showing us a SAS data set anyway, but some type of other view like possibly Excel, which simply is not acceptable).

So really really really follow the instructions given and provide us with DATA step code that illustrates your data.

[ballardw]

Your requirement appears to be

Where somevariablename = "Some value";

Available in many procedures. Try it with proc freq for simple test.

Or the dataset option

proc something data=yourdatasetname (where=(somevariable='Some Value')) ;

What the variable name actually is and the actual value in your data is the question.

Equality is very strong requirement. If you use ="Corrective" then "Corrective Final" is not equal and won't match. Neither will "corrective" "CORRECTIVE" "cOrrective" or " Corrective" (see the space at the front). SAS will ignore trailing blanks with equal.

[Patrick]

@cort125 

You've got already the answer how to filter on row level.

"I also want to create a code where the specimen number has a Report Status as Corrective only but was never Final."

Above statement makes me think that you also want a filter on group level. But you don't provide sample Have data and expected result for this question so I'm not going to provide an answer based on guesses.

Please provide your sample data via a working SAS datastep that creates such data and post it using the running man icon .

This allows us to spend the time to create and test code that answers your question instead of spending the time converting some html table to a SAS dataset.

[Tom]

I suspect that you have not described the data sufficiently as it seem trivial to me.

First let's convert your listing into an actual dataset.  Let's use easier variable names.

data have;
  input ID $ Specimen :$10. Date :mmddyy. Status &:$20.;
  format date yymmdd10. ;
cards;
12345  W2345678   1/5/2020   Corrective Final
23456  M9876799   3/4/2023   Corrective
84569  N45678987  5/22/2022  Corrective Final
78766  P73839303  6/9/2022   Final
35473  R37390202  4/7/2023   Corrective
;

To produce your desired result just test for that value.

proc print data=have;
  where status='Corrective Final';
run;

Result:

Obs     ID      Specimen           Date         Status

 1     12345    W2345678     2020-01-05    Corrective Final
 3     84569    N45678987    2022-05-22    Corrective Final

[ajay35]

data have;
input ID $ Specimen :$10. Date :mmddyy. Status &:$20.;
format date yymmdd10. ;
cards;
12345 W2345678 1/5/2020 Corrective Final
23456 M9876799 3/4/2023 Corrective
84569 N45678987 5/22/2022 Corrective Final
78766 P73839303 6/9/2022 Final
35473 R37390202 4/7/2023 Corrective
;

data want (drop=cntw);
set have;
cntw=countw(Status);
if cntw >1 then output;
run;