handover a list in Proc SQL

[marieK]

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

[Ksharp]

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]

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]

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]

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

[Ksharp · Accepted solution]

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]

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

[marieK]

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

[Ksharp]

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]

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]

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.