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

Hey Guys,

i have a problem in Proc SQL. I would like to compare a Variable with diagnoses with a list of ICD-Codes, to select just the observations which had a diagnose contained in this list.

Here a little example:

%Macro test (dx=);

proc sql;

     create table diag_heart as

     select patid, diag;

     from table1

     where diag IN &dx;

%Mend test;

%test(dx=('I05%','I06%','I07%'));

%test(dx=('G051%'));

The problem is that the IN function doesnt know the %. Do you have any ideas?

regards, marie

1 ACCEPTED SOLUTION

Accepted Solutions
Ksharp
Super User

This is an example:

%Macro test (dx=);
%let x=%qscan(&dx,1,%str(%(%)));
%let list=%sysfunc(tranwrd(&x,%str(,),%str( or name like ) ));
%put &x &list;
proc sql;
     select name
     from sashelp.class
     where name like &list;quit;
%Mend test;
%test(dx=('A%','B%'));


Ksharp

View solution in original post

10 REPLIES 10
Ksharp
Super User

Yes. in operate do not recognize %. You should use like.

But for your situation, it is not suited for like operation, except you hard code like :

diag like 'A%' and diag like 'B%' ....

There is also another operation in: to meet your demand. But it is not valid in proc sql .

It is valid in data step.

%let list='B','A';
data class;
 set sashelp.class ;
   if name in: (&list);
run;

Ksharp

marieK
Obsidian | Level 7

Thank you very much for your help.

i already tried this version but then i have runtime-problem with 6 million patients because the sql-statement also need to do another select statement (has to select the sample-patientes from a big database):

at the moment the macro looks like:

%Macro test (dx=);

proc sql;

     create table diag_heart as

     select patid, diag;

     from table1

     where patid in (select patid from sample);

quit;

data diag_heart;

set diag_heart;

if diag IN:(&dx);

run;

%Mend test;

%test(dx=('I05%','I06%','I07%'));

%test(dx=('G051%'));

This macro is exactly doing what i want but really takes to long. If i dont write it as macro and put all together into a sql-statement (like you told above with the like statement) it is a lot faster..

but i need a macro :smileygrin:

do you see a possibility to combine these 2 steps?

CMilena
Calcite | Level 5

Hi Mariek,

The problem start from = symbol.

You can try this step

%Macro test (dx);

proc sql;

     create table diag_heart as

     select patid, diag;

     from table1

     where diag IN &dx;

quit;

%Mend test;

%test (( "I05%","I06%","I07%"));

marieK
Obsidian | Level 7

Thanks for your answer but the SQL IN-function cant handle the %-sign..:( this i tried too Smiley Happy

Ksharp
Super User

This is an example:

%Macro test (dx=);
%let x=%qscan(&dx,1,%str(%(%)));
%let list=%sysfunc(tranwrd(&x,%str(,),%str( or name like ) ));
%put &x &list;
proc sql;
     select name
     from sashelp.class
     where name like &list;quit;
%Mend test;
%test(dx=('A%','B%'));


Ksharp

marieK
Obsidian | Level 7

uuhuuhuh Smiley Happy looks scary Smiley Happy i willl try!! thank you soo much!

marieK
Obsidian | Level 7

you are my hero Smiley Happy works fine! but it also takes sooo long.ggrrr. is it because of the macro?

Ksharp
Super User

I am not quit sure. Maybe it is because of your large table mainly.

Also processing with macro variable is to waste time a little.

Ksharp

Tom
Super User Tom
Super User

Most likely it is because the LIKE operator is not that efficient?

Is there a reason you are using SQL instead of a DATA step?

If you are using a data step then you can use the : modifier to do truncated comparison.

data want ;

   set have ;

   if diag in: ('I05' 'I06' 'I07') ;

run;

If you have to use SQL see if using substr makes it faster (but it might make it slower!);

...

  where substr(diag,1,3) = 'I05'

     or substr(diag,1,3) = 'I06'

     or substr(diag,1,3) = 'I07'

...

marieK
Obsidian | Level 7

thanks for your answers..our problem has been solved from alone over night. it might have been just a server-problem?! runtime is now the same with and without macro.

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!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

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
  • 10 replies
  • 1374 views
  • 4 likes
  • 4 in conversation