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
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
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
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?
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%"));
Thanks for your answer but the SQL IN-function cant handle the %-sign..:( this i tried too
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
uuhuuhuh looks scary i willl try!! thank you soo much!
you are my hero works fine! but it also takes sooo long.ggrrr. is it because of the macro?
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
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'
...
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.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.