using the Where statement

[hjjijkkl]

Hello,

I am trying to use the Where Statement in a proc freq to select specific subject but, my program is not working. please see the program below.

I am trying to find individ with no missing mod_date but missing status=1 but not missing status=2

proc frq data=temp;

where not(missing(mod_date))and missing(status=1) and status=2;

run;
 

[PaigeMiller]

Does the LOG contain errors? Can you determine from that what is wrong?

Did you type the code properly? (No you didn't, there is no such thing as PROC FRQ, and possibly other errors as well)

What does "missing status=1 but not missing status=2" really mean? Do you have a variable named status or a variable named missing_status? What are your variable names in the SAS data set?

[ballardw]

---

@hjjijkkl wrote:

Hello,

I am trying to use the Where Statement in a proc freq to select specific subject but, my program is not working. please see the program below.

I am trying to find individ with no missing mod_date but missing status=1 but not missing status=2

proc frq data=temp;

where not(missing(mod_date))and missing(status=1) and status=2;

run;
 

---

This bit of your where is never true:

missing(status=1) 

What SAS does with something like that is compare the variable STATUS to the value 1. If Status is missing or any value except 1 the comparison returns 0, otherwise a value of 1. So you are basically asking to see if 0 is missing (false) or 1 is missing (false).

Since a variable will only have one value per record, which is what Where looks at, then you need to describe, i.e. provide data with values, which records you want processed because "missing status=1 but not missing status=2" makes absolutely no sense in a per record context.

If you mean to look across all the values and apply a rule then you will need to subset your data prior to proc freq but how to do that really means we need to see some example data.

[Amir]

Hi @hjjijkkl,

If you genuinely are trying to use the missing() function then check the documentation which also shows some examples of how to use it.

Try posting rows of representative example data, showing which rows you want to be processed by proc freq.

Kind regards,

Amir.

[Kurt_Bremser]

status=1

is a boolean expression that results in either 0 or 1, so it can never be missing. Since you combine all conditions with AND, the whole condition will never be true.

[Tom]

Are you trying to check if the vlaue of STATUS is either 1 or 2? 

If so you might want to use the IN operator.  This will find records with STATUS of either 1 or 2 that do not have a missing MOD_DATE.

where not missing(mod_date) and status in (1 2);

To test for missing you can use period.  This will find records with status of missing or 2.

where not missing(mod_date) and status in (. 2);

But watch out for special missing values like .A,.B,...,.Z and ._ .  If your values might have special missing values might want to code that second example using the missing() function instead.

where not missing(mod_date) and (status=2 or missing(status));

[hjjijkkl]

Thank you!

Yes, I have three variables ( ID, Status, Mod_date). the status contains obs (1, 2, 3). I am trying to find the number IDs missing status 1 but have status 2 and 3. 

[PaigeMiller]

Please show us a portion of your data.

[Tom · Accepted solution]

---

@hjjijkkl wrote:

Thank you!

Yes, I have three variables ( ID, Status, Mod_date). the status contains obs (1, 2, 3). I am trying to find the number IDs missing status 1 but have status 2 and 3. 

---

You cannot do that with a simple WHERE as you need to check across observations.

You might want to aggregate the data to a single observation per ID.

Here is method using data step, note it requires that data is sorted by ID.

data want;
  set have;
  by id;
  retain has1 has2 has3 ;
  if first.id then call missing(has1,has2,has3);
  if status=1 then has1=1;
  if status=2 then has2 =1;
  if status=3 then has3=1;
  if last.id;
  keep id has1-has3;
run;

Now you test is just (HAS2 and HAS3 and NOT HAS1) .

[Amir]

Hi @hjjijkkl,

Further to the explanation @ballardw has given about comparing values, you have to be sure the types of the variables / values you are comparing are the same, otherwise you might get an error such as:

34         proc freq data = sashelp.class;
35            where missing(name=1);
ERROR: WHERE clause operator requires compatible variables.
36         run;

Kind regards,

Amir.