topic Re: WHERE CONDITION in SAS Programming

WHERE CONDITION

robertrao — Mon, 17 Oct 2016 13:13:29 GMT

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;

Re: WHERE CONDITION

Patrick — Mon, 10 Oct 2016 14:25:05 GMT

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;

Re: WHERE CONDITION

ChrisNZ — Mon, 10 Oct 2016 08:32:55 GMT

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;

Re: WHERE CONDITION

Patrick — Mon, 10 Oct 2016 08:40:51 GMT

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;

Re: WHERE CONDITION

Ksharp — Mon, 10 Oct 2016 10:43:39 GMT

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

Re: WHERE CONDITION

Astounding — Mon, 10 Oct 2016 13:26:51 GMT

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.

Re: WHERE CONDITION

robertrao — Mon, 17 Oct 2016 13:14:05 GMT

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

Thanks

Re: WHERE CONDITION

ChrisNZ — Mon, 10 Oct 2016 20:58:27 GMT

@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

Re: WHERE CONDITION

Patrick — Tue, 11 Oct 2016 00:37:19 GMT

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;

Re: WHERE CONDITION

Ksharp — Tue, 11 Oct 2016 11:14:58 GMT

Sorry. My bad.