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? ... View more

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 ... View more

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; ... View more

Hi RW9. I use SAS enterprise guide 5.1 I actually had your thought before about creating a brand flag in the dataset. Because of some business reasons we wont be able to add that flag into the table. I think the above methods suits me if it allows to choose multiple brands in the prompts? ... View more

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. ... View more

Hi RW9 Does this work if user chose to go with only one brand as brand two wont be initialized? %let brand1=bmw; %let brand2=mercedes;     and   (upcase(NAME) in ("NAME","AGE","SEX") or index(upcase(NAME),upcase("&BRAND1."))>0 or index(upcase(NAME),upcase("&BRAND2."))>0); ... View more

Hi all, The reason I want  this program is I want to create a prompt for the brand where user selects one or multiple brands from a static list when kicking off the program  and the final dataset should end up with that user chosen brand specific data. I do not want the users to go in the program and edit the data. Hope I am clear? ... View more

Hi Xia, I did not understand that. Please can you be more specific? ... View more

Hi RW9, That seems to be working. What if I want to select multiple brands? (ex: bmw and Mercedes) ... View more

Hi RW9, Thank you very much for your quick response. Please do you mind explain this line of code?    (upcase(NAME) in ("NAME","AGE","SEX") or index(upcase(NAME),upcase("&BRAND."))>0); In this example we only have 5 or 6 variables. What if we have like 100 variables? Do we have to manually enter all those variable names? Message was edited by: Kiran Maddi ... View more

Hi Reeza, what you said is exactly right in a way. The templates are missing for those brands. I could not have done it with out your inputs. Thanks a lot for all your help. Keep doing what you are doing. All the best. ... View more

Right I got you. I will keep that in mind and tomorrow will try to find out if the excel is not created for those that are not working. I will keep you updated. Thanks a ton for your help. you are a star. ... View more

You may be right Reeza. But, One thing is still confusing.If you see my actual post above. Why there are some tables in SAS EG(in a library called myexcel) called DI Graphs$'Print_Area, Agg Graphs$'Print_Area  etc for BIS unlike Halifax? ... View more

Where do you see that it is pre-created for BIS? ... View more