BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
robertrao
Quartz | Level 8

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;

1 ACCEPTED SOLUTION

Accepted Solutions
Patrick
Opal | Level 21

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

9 REPLIES 9
Patrick
Opal | Level 21

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
Tourmaline | Level 20

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
Opal | Level 21

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
Super User

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

Ksharp
Super User

Sorry. My bad.

Astounding
PROC Star

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
Quartz | Level 8

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

 

Thanks

Patrick
Opal | Level 21

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;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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