DATA Step, Macro, Functions and more

WHERE CONDITION

Accepted Solution Solved
Reply
Super Contributor
Posts: 1,040
Accepted Solution

WHERE CONDITION

[ Edited ]

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;


Accepted Solutions
Solution
‎10-10-2016 10:23 AM
Respected Advisor
Posts: 3,887

Re: WHERE CONDITION

[ Edited ]

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;

View solution in original post


All Replies
Solution
‎10-10-2016 10:23 AM
Respected Advisor
Posts: 3,887

Re: WHERE CONDITION

[ Edited ]

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;
PROC Star
Posts: 1,558

Re: WHERE CONDITION

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;

 

Respected Advisor
Posts: 3,887

Re: WHERE CONDITION

[ Edited ]

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;
Super User
Posts: 9,671

Re: WHERE CONDITION

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

PROC Star
Posts: 1,558

Re: WHERE CONDITION

Super User
Posts: 9,671

Re: WHERE CONDITION

Sorry. My bad.

Super User
Posts: 5,079

Re: WHERE CONDITION

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.

Super Contributor
Posts: 1,040

Re: WHERE CONDITION

[ Edited ]

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

 

Thanks

Respected Advisor
Posts: 3,887

Re: WHERE CONDITION

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;
☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 9 replies
  • 421 views
  • 4 likes
  • 5 in conversation