topic handover a list in Proc SQL in SAS Procedures

handover a list in Proc SQL

marieK — Thu, 15 Sep 2011 08:30:43 GMT

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

handover a list in Proc SQL

Ksharp — Thu, 15 Sep 2011 08:42:08 GMT

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

handover a list in Proc SQL

marieK — Thu, 15 Sep 2011 08:53:38 GMT

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?

handover a list in Proc SQL

CMilena — Thu, 15 Sep 2011 10:02:07 GMT

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%"));

handover a list in Proc SQL

Ksharp — Thu, 15 Sep 2011 10:53:31 GMT

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

handover a list in Proc SQL

marieK — Thu, 15 Sep 2011 11:15:54 GMT

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

handover a list in Proc SQL

marieK — Thu, 15 Sep 2011 11:16:38 GMT

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

handover a list in Proc SQL

marieK — Thu, 15 Sep 2011 12:21:17 GMT

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

handover a list in Proc SQL

Ksharp — Fri, 16 Sep 2011 01:39:22 GMT

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

Re: handover a list in Proc SQL

Tom — Fri, 16 Sep 2011 01:59:20 GMT

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'

...

Re: handover a list in Proc SQL

marieK — Fri, 16 Sep 2011 08:30:40 GMT

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.