Hi All,
I think this code is better for me:
%let brand= bmw;
&brand;
proc sql;
select distinct NAME
into :KEEP_LIST separated by " "
from DICTIONARY.COLUMNS
where LIBNAME="WORK"
and MEMNAME="HAVE"
and (index(upcase(NAME),upcase("&BRAND."))>0);
quit;
%put &keep_list;
data want;
set have (keep= name--sex &KEEP_LIST.);
run;
Question: As I have already mentioned that I am creating prompts for the user to choose the brands. I am not entirely sure if this line of code works when user selects more than one brand?
and (upcase(NAME) in ("NAME","AGE","SEX") or index(upcase(NAME),upcase("&BRAND1."))>0 or index(upcase(NAME),upcase("&BRAND2."))>0);
Is there anyway we can use 'in' for the &brand? (ex: (index(upcase(NAME),in upcase("&BRAND."))>0);
Thank you all for your helpful suggestions.
Hi all,
Any ideas on how can we make the below code to accommodate multiple brands in a single macro variable(&brand) ?
Note: user enters brand names in the prompt either single or multiple.
proc sql;
select distinct NAME
into :KEEP_LIST separated by " "
from DICTIONARY.COLUMNS
where LIBNAME="WORK"
and MEMNAME="HAVE"
and (index(upcase(NAME),upcase("&BRAND."))>0);
quit;
Hi,
Well, you could parse the list with %sysfuncs and tranwrds. I personally don't like that way. I would first get your list of parameters and put them into a dataset. You can apply checks, upcase etc. at that point and make the SQL code easier to read:
%let brand=bmw mercedes;
data list (drop=i);
length brand $20;
i=1;
do while (scan("&BRAND.",i," ") ne ""); /* Do any checks or processing here */
brand=upcase(scan("&BRAND.",i," "));
output;
i=i+1;
end;
run;
proc sql;
select distinct NAME
into :KEEP_LIST separated by " "
from DICTIONARY.COLUMNS
where LIBNAME="WORK"
and MEMNAME="HAVE"
and scan(upcase(NAME),1,"_") in (select distinct BRAND from LIST); /* Assumes there is an _ delimiter between option and any other text */
quit;
HI RW9,
This is perfectly working in the case where we enter brand names in the %let statement. Where as using prompts, it is creating WANT dataset with only the first brand chosen ignoring the second brand,
Here is the SAS generated macro variables using prompt values from the log:
%LET Brand = bmw;
%LET Brand_count = 2;
%LET Brand0 = 2;
%LET Brand2 = audi;
%LET Brand1 = bmw;
data want;
set have (keep= name--sex &brand);
run;
Message was edited by: Kiran Maddi
You need to process the automatically created variables and take care of one special situation: if a user only selects one value from the list,than the variables Brand0 and Brand1 are not created at all.
/* untested code */
data work.list(drop= i);
length Brand $ 20;
if &Brand_count = 1 then do;
Brand = upcase("&Brand");
output;
end;
else do;
do i = 1 to &Brand_Count;
Brand = upcase(resolve(cats("Brand", i)));
output;
end;
end;
run;
Hi Andreas;
Used your code. The problem I am having is
When user chooses bmw and Mercedes from the prompt list. SAS is creating the below automatic varibles:
%LET _SASPROGRAMFILE=;
%LET Brand = bmw:;
%LET Brand_count = 2;
%LET Brand0 = 2;
%LET Brand2 = mercedes:;
%LET Brand1 = bmw:;
As your code has &brand (Brand = upcase(resolve(cats("Brand", i)));) in it, it is picking bmw. How can we make the code to use &brand1 and &brand2 instead of just brand with out hardcoding? Hope my question is clear?
Hi,
I don't use EG, so I can't cover every eventuality. If you get multiple then maybe something like:
%let brand1=bmw;
%let brand2=mercedes;
proc sql;
select distinct NAME
into :KEEP_LIST separated by " "
from DICTIONARY.COLUMNS
where LIBNAME="WORK"
and MEMNAME="HAVE"
and scan(upcase(NAME),1,"_") in (select upcase(VALUE) from SASHELP.VMACRO where substr(NAME,1,5)="BRAND");
quit;
Hi RW9,
Still stuck with this issue. Tried your program:
WARNING: Apparent symbolic reference KEEP_LIST not resolved.
Am I doing anything wrong here?
What is the code you are running, if you run this:
%let brand1=bmw;
%let brand2=mercedes;
proc sql;
select distinct NAME
into :KEEP_LIST separated by " "
from DICTIONARY.COLUMNS
where LIBNAME="WORK"
and MEMNAME="HAVE"
and scan(upcase(NAME),1,"_") in (select upcase(VALUE) from SASHELP.VMACRO where substr(NAME,1,5)="BRAND");
quit;
%put &KEEP_LIST.;
It should work fine, which would indicate that you either have a) a scope issue, i.e. the macro variable is local to a certain scope, of b) EG is doing something differently.
Hi RW9.
I think you are right. It's the scope issue. How can we make them global? I don't the brand1 and brand2 in the sashelp.vmacro as they were auto generated by the prompts.
As mentioned I don't use EG so don't know how it does things. Normally, you would put %global brand1; at the top of the program. Alternatively, in their scope, save the parameters to a dataset for future use.
Hi all,
Sorry for the delay in coming back. Was on holiday and just came back. The below code worked for me.
proc sql;
select distinct upcase(VALUE)
into :KEEP_LIST separated by " "
from SASHELP.VMACRO
where NAME in ('BRAND','BRAND1','BRAND2','BRAND3','BRAND4','BRAND5','BRAND6','BRAND7','BRAND8','BRAND9,''BRAND10','BRAND11','BRAND12','BRAND13');
quit;
I have 13 brands in the prompts to choose. If user chooses only one brand then brand will be &brand. if multiple it will be &brand1 &brand2 and so on.
Thank you all for your help. You all are wonderful
where NAME EQT 'BRAND'
Hi _null_
There is a reason I put brand 1 to brand 13. I know that is not ideal to do. The reason is somehow when running the prompts sas is creating an automatic variable called brand_count and it is existed only during the execution pahse. So if I use name like operator it grabs brand_count as well which i dont want it. I have also ised where name not equal to brand_count. That did not find the brand_ count in sashelp.vmacro
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.