ASA Connect

Why does the following SAS code not include in the new SAS dataset 'AE_related' all the observations that fulfil the conditions set forth in the IF statement, rather it only includes some observations? Is there a mistake in the code?

data AE_related;
set AE;
if VAR1 in ('PossiblyRelated', 'ProbablyRelated', 'Related') | VAR2 in ('PossiblyRelated', 'ProbablyRelated', 'Related') | VAR3 in ('PossiblyRelated', 'ProbablyRelated', 'Related');
run;

------------------------------
Benjamin A. Levinson
Research Scientist
NYU Grossman School of Medicine
------------------------------

I don't see anything wrong with your SAS code.

Those checks are case sensitive so if the values for some records use different cases for the specified phrases, then those records wouldn't be kept.

You can rule out case differences by using the following if statement:

if upcase(VAR1) in ('POSSIBLYRELATED', 'PROBABLYRELATED', 'RELATED') | VAR2 in ('POSSIBLYRELATED', 'PROBABLYRELATED', 'RELATED') | VAR3 in ('POSSIBLYRELATED', 'PROBABLYRELATED', 'RELATED');

If that still doesn't work, then the only thing I can think of is encoding issues with your variable values. 

I also wanted to point out that the in statement looks for EXACT strings.

if you are looking for the existence of a substring within the values of VAR1 through VAR3 then you need to search for the occurrence of those substrings.

if(index(upcase(var1),'POSSIBLYRELATED')>0) | index(upcase(var1),'PROBABLYRELATED')>0)  | index(upcase(var1),'RELATED')>0) 

| index(upcase(var2),'POSSIBLYRELATED')>0) | index(upcase(var2),'PROBABLYRELATED')>0)  | index(upcase(var2),'RELATED')>0) 

| index(upcase(var3),'POSSIBLYRELATED')>0) | index(upcase(var3),'PROBABLYRELATED')>0)  | index(upcase(var3),'RELATED')>0) );

------------------------------
David Wilson
Senior Director, Statistics
RTI International
------------------------------

Ben Hi!

Without a reproducible example it's a bit difficult to see what's happening, but in general when working in SAS it's best to be overly verbose. ie

data want;

  set have;

  if AR1 in ('PossiblyRelated', 'ProbablyRelated', 'Related')

    OR VAR2 in ('PossiblyRelated', 'ProbablyRelated', 'Related')

    OR VAR3 in ('PossiblyRelated', 'ProbablyRelated', 'Related')

   THEN OUTPUT;

run;

Or in sql:

proc sql;

  create table want as

  select distinct * from have

  where AR1 in ('PossiblyRelated', 'ProbablyRelated', 'Related')

    OR VAR2 in ('PossiblyRelated', 'ProbablyRelated', 'Related')

    OR VAR3 in ('PossiblyRelated', 'ProbablyRelated', 'Related')

;

quit;

Cheers,

AB

------------------------------
Adam Batten
Lead Statistician & President
AB EVERGREEN ANALYTICS LLC
------------------------------

Case selection based on character strings is notoriously fraught with problems. In addition to the very well thought out suggestions already posted I would suggest using the trim and compress SAS functions to eliminate hidden trailing or leading blanks. If the strings were not machine generated you can encounter all sorts of problems. I also suggest dumping some records that should be selected and are not and checking them in a HEX editor to find the reason for the failing match. 

Cheers, Tim Kenney.

------------------------------
Timothy Kenney
President
Kenney IS Consulting, Inc.
------------------------------

Dear all,

Thank you very much to each and every one of you who answered my question with your comments, insights, and suggestions.

Happy Holidays!

Benjamin

------------------------------
Benjamin A. Levinson
Research Scientist
NYU Grossman School of Medicine
------------------------------