WHERE CONDITION

[robertrao]

hello,

how do i include the if conditions in the same query?

i could not put the braces correctly and so i was doing a seperate data step because of some syntax errors...

proc sql;

create table hepmed as

select *

from med(keep= ordering_date order_status_c display_name

where=('06SEP2016'D <= ordering_date <= '25SEP2016'D

and index(upcase(display_name),'HEP') > 0

and order_status_c in (2,3,)

))

;

quit;

run;

data hepmed_2;

set hepmed ;

if index(upcase(DISPLAY_NAME),'.LINE') > 0 or

index(upcase(DISPLAY_NAME),'. LINE') > 0 or

index(upcase(DISPLAY_NAME),'ASSAY') > 0 then delete;

;

run;

[Patrick · Accepted solution]

If using Proc SQL then try and stick with real SQL syntax and don't combine it with data set options like med(keep=... where=()).

...and of course there is always the justified exception to the rule...

proc sql;
  create table hepmed as
  select 
    ordering_date, 
    order_status_c, 
    display_name
  from med
  where
   '06SEP2016'D <= ordering_date <= '25SEP2016'D
    and index(upcase(display_name),'HEP') > 0
    and order_status_c in (2,3)
    and upcase(DISPLAY_NAME) not like '%ART.LINE%' 
    and upcase(DISPLAY_NAME) not like '%ART. LINE%' 
    and upcase(DISPLAY_NAME) not like '%D10W%' 
  ;
quit;

[ChrisNZ]

If you want to keep data step options:

proc sql;

create table hepmed as

select *

from med(keep= ordering_date order_status_c  DISPLAY_NAME

        where=('06SEP2016'D <= ordering_date <= '25SEP2016'D

              & find(DISPLAY_NAME,'HEP','it')

              & order_status_c in (2,3,.)

              & ^find(DISPLAY_NAME,'ART.LINE','it')

              & ^find(DISPLAY_NAME,'ART. LINE','it')

              & ^find(DISPLAY_NAME,'D10W','it')

              & ^find(DISPLAY_NAME,'D5W','it')

              & ^find(DISPLAY_NAME,'SOAK','it')

              & ^find(DISPLAY_NAME,'FLUSH','it')

              & ^find(DISPLAY_NAME,'IRRIGATION','it')

              & ^find(DISPLAY_NAME,'DIANEAL','it')

              & ^find(DISPLAY_NAME,'HEPARIN ASSAY','it')

          ));

quit;

[Patrick]

If you want to keep the data step option as @ChrisNZ proposes then I'd have it in a data step.

data hepmed;
  set med(keep= ordering_date order_status_c  DISPLAY_NAME
        where=('06SEP2016'D <= ordering_date <= '25SEP2016'D
              & find(DISPLAY_NAME,'HEP','it')
              & order_status_c in (2,3,.)
              & ^find(DISPLAY_NAME,'ART.LINE','it') 
              & ^find(DISPLAY_NAME,'ART. LINE','it')
              & ^find(DISPLAY_NAME,'D10W','it') 
              & ^find(DISPLAY_NAME,'D5W','it') 
              & ^find(DISPLAY_NAME,'SOAK','it') 
              & ^find(DISPLAY_NAME,'FLUSH','it') 
              & ^find(DISPLAY_NAME,'IRRIGATION','it')
              & ^find(DISPLAY_NAME,'DIANEAL','it') 
              & ^find(DISPLAY_NAME,'HEPARIN ASSAY','it')
          ));
run;

[Ksharp]

Use HAVING clasue instead of WHERE clause due to have aggregrate function INDEX() .

[ChrisNZ]

@Ksharp there are no string aggregration functions is SAS.
We need some though, see https://communities.sas.com/t5/SASware-Ballot-Ideas/create-string-summary-functions/idi-p/288035

[Ksharp]

Sorry. My bad.

[Astounding]

While you have received many valid comments, the one piece that absolutely has to change is the extra comma here:

in (2,3,)

Your original code might even work if you removed that final comma.

[robertrao]

Thank you very much for all the valuable suggestions. It worked for me using "NOT LIKE".

Thanks

[Patrick]

Here you go. Below code is logically the same and returns the same result than the solution you've accepted.

proc sql;
  create table hepmed as
  select 
    ordering_date, 
    order_status_c, 
    display_name
  from med
  where
   '06SEP2016'D <= ordering_date <= '25SEP2016'D
    and index(upcase(display_name),'HEP') > 0
    and order_status_c in (2,3)
    and NOT
      (
        upcase(DISPLAY_NAME)    like '%ART.LINE%' 
        or upcase(DISPLAY_NAME) like '%ART. LINE%' 
        or upcase(DISPLAY_NAME) like '%D10W%'
      )

  ;
quit;