Help using Base SAS procedures

handover a list in Proc SQL

Accepted Solution Solved
Reply
Contributor
Posts: 44
Accepted Solution

handover a list in Proc SQL

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


Accepted Solutions
Solution
‎09-15-2011 06:53 AM
Super User
Posts: 9,691

handover a list in Proc SQL

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


All Replies
Super User
Posts: 9,691

handover a list in Proc SQL

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

Contributor
Posts: 44

handover a list in Proc SQL

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 INSmiley Sad&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?

Occasional Contributor
Posts: 7

handover a list in Proc SQL

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

Contributor
Posts: 44

handover a list in Proc SQL

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

Solution
‎09-15-2011 06:53 AM
Super User
Posts: 9,691

handover a list in Proc SQL

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

Contributor
Posts: 44

handover a list in Proc SQL

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

Contributor
Posts: 44

handover a list in Proc SQL

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

Super User
Posts: 9,691

handover a list in Proc SQL

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

Super User
Super User
Posts: 6,502

Re: handover a list in Proc SQL

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'

...

Contributor
Posts: 44

Re: handover a list in Proc SQL

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.

🔒 This topic is solved and locked.

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

Discussion stats
  • 10 replies
  • 260 views
  • 4 likes
  • 4 in conversation