Choosing variables based on condition

[KiranMaddi]

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.

[KiranMaddi]

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;

[RW9]

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;

[KiranMaddi]

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

[andreas_lds]

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;

[KiranMaddi]

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?

[RW9]

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;

[KiranMaddi]

Hi RW9,

Still stuck with this issue. Tried your program:

WARNING: Apparent symbolic reference KEEP_LIST not resolved.

Am I doing anything wrong here?

[RW9]

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.

[KiranMaddi]

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.

[RW9]

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. 

[KiranMaddi · Accepted solution]

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

[data_null__]

where NAME EQT 'BRAND'

[KiranMaddi]

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