Hello, i am a verry new and verry basic user, and i want to know if it possible to have different vallues in a where function following a contains cmd, ex:
proc sort data=work.l2b1;
where F='DE' and A contains 'ciproflox' or
F='FR' and A contains 'OFLOX';
by J;
How i could put more values/prod names after contains, smth like this: contains 'cipro' 'ator' 'Ome' 'etc',
I am trying to extract from a datat set only the lines that have country of incidence FR ( F=country) and at least one from the product list.
Like i have mentioned i am verry new with SAS and i dont have a programmer background, i dont even know if contains can do what i want.
THX.
If all of your coditions are a combination of F and A variables you can use:
where upcase(cat(F,A)) in ("DECIPROFLOX" , "FROFLOX" , ... );
It will be easier for me to understand if its possible to have a string of values following a contains:
contains A B C D E, and how exactly i writhe them so i wont get an error, because ive tried :
Contains ("A", "B", "C", "D") and doesent work.
Put your condition values into a dataset, and you can use contains or similar in a where condition of a SQL join.
Unfortunately i dont know how to do that, 0 experience with sql.
So if someone can tell me if is possible to have more then 1 observation after contains/like,
example of what im looking for: like ('a', 'b', 'c'), btw i've tried this and is not working for me.
My code is smth like :
proc sort data=work.l2b1;
where F='DE' and A like ('%ciproflox%'; '%atorv%','%ola%');
by J;
run;
if i use where F='DE' and A like '%ciproflox%'; it works, but i cant get at least 2 obs afther like, dont know how to group them
As much as I know there is no function like what you want.
but you can do it using a macro program.
define the macro outside and before the step to run:
%macro check_for(varF=, varA=);
%let n=%sysfunc(countw(&varA));
%let varF = %upcase(&varF);
%let varA = %upcase(&varA);
if F = "&varF" and
%do i=1 %to &n;
/* A contains... */
findw(upcase(A) , "%scan(&varA,&i)")
/* index(upcase(A) , "%scan(&varA,&i)") */
%if &i < &n %then %str( or );
%end;
then output;
%mend check_for;
If you want the check for a substring, not a whole word, then replace the FINDW function by INDEX function.
creating a test data and using the macro:
data test;
F="DE"; A="any ciproflox med"; output;
F="DE"; A="any non oflox med"; output;
F="FR"; A="OFLOX or BFLOX or CFLOX"; output;
F="FR"; A="eflox or dflox oe Fflox"; output;
;
run;
data result;
set test;
%check_for(varF=DE, varA=cipro);
%check_for(varF=FR, varA=oflox fflox);
run;
Run both functions - with FINDW and with INDEX and compare results.
Tank yiu verry much, i have managed to aplly your macro to my dataset, but im still having some problems:
1 one is that when A have more then 1 observation (Ex: A=ciprofloxcin atorvastatin) even if F=DE (F=country, DE=Germany), i still get results from outside DE (ex IT, FR, countries were the second product "atorvastatine" is present).
the code is:
data result;
set l2b1;
%check_for(varF=DE, varA=ciprofloxacin atorvastatin);
run;
proc print data=work.result;
run;
2. i would like to be abble to search for a part of the word not exactly the word Ex; %ator not atorvastatin, so to get any obs that start with ator.
3. I dident understand the comment
/* A contains... */ was that put there to explain that
findw(upcase(A) , "%scan(&varA,&i)") or /* index(upcase(A) , "%scan(&varA,&i)") */ was the cointain function i was looking for.
Thank you.
I'll start with your third note - the comment means that next two lines are are supposed to do what you want.
As there is no function CONTAIN (nor CONTAINS) I can use each of the two other functions:
FINDW - search for a whole word limitted by spaces.
INDEX - search for a substring.
for example:
A = "ciproflox".
result1 = findw(A , "cipro") will be 0 (means not found).
result2 = index(A, "cipro") will be 1 (the position where the substring starts).
It seems that you need the INDEX variation of the macro program.
I have added ( ) to fix the problem of getting non DE observations.
%macro check_for(varF=, varA=);
%let n=%sysfunc(countw(&varA));
%let varF = %upcase(&varF);
%let varA = %upcase(&varA);
if F = "&varF" and (
%do i=1 %to &n;
index(upcase(A) , "%scan(&varA,&i)")
%if &i < &n %then %str( or );
%end; ) then output;
%mend check_for;
I suggest that you add options mprint; in your code and look at the log to see what have this macro program done:
options mprint;
data result;
set l2b1;
%check_for(varF=DE, varA=ciprofloxacin atorvastatin);
run;
options nomprint;
proc print data=work.result;
run;
Sry for my late reply, I wanted to tank you and to say i've managed to make the macro work for my dataset. Tank you all for the support. Have a good and blessed day.
Please assign the correct answer as solution so that others have it also.
There is no simple function/operator for that type of test.
I would just start by combining multiple tests.
proc sort data=work.l2b1 out=want;
where (F='DE'
and (lowcase(A) contains 'ciproflox'
or lowcase(A) contains 'cipro'
)
)
or (F='FR' and A contains 'OFLOX')
;
by J;
run;
If you are familiar with regular expressions you might be able to use one or more of the prx.... functions to do your search.
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 how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.