topic Re: Choosing variables based on condition in SAS Programming

Choosing variables based on condition

KiranMaddi — Thu, 04 Jun 2015 08:12:48 GMT

Hello SAS gurus,

Could someone please help me with the following query. Thanks in advance

For instance I have a dataset like this

Name Age sex BMW BMW_Premium BMW_Standard AUDI Mercedes Mercedes_card Vauxhall Toyota Toyota_Rnw
x x x x x x x x x x x x

Now I want to see only BMW data along with demographic data. How do I select only brand specific variables along with Name Age sex using macros or any other method?

for instance user should be able to select what he wants by entering the brand name in %let(Ex: %let brand = BMW)

Re: Choosing variables based on condition

RW9 — Thu, 04 Jun 2015 08:38:33 GMT

Hi,

The below uses your columns naming which is not consitent, hence I have to query the dataset first to get variable names, e.g. BMW and BMW_PREMIUM, but no AUDI_PREMIUM etc. Having consistent column naming would simplify this.

data have;

name="abcd"; age=20; sex="M"; bmw="3 series"; bmw_premium="Y"; audi="N"; mercedes="N"; output;

name="efj"; age=20; sex="M"; bmw="3 series"; bmw_premium="Y"; audi="A8"; mercedes="SLR"; output;

run;

%let brand=bmw;

proc sql;

select distinct NAME

into :KEEP_LIST separated by " "

from DICTIONARY.COLUMNS

where LIBNAME="WORK"

and MEMNAME="HAVE"

and (upcase(NAME) in ("NAME","AGE","SEX") or index(upcase(NAME),upcase("&BRAND."))>0);

quit;

data want;

set have (keep=&KEEP_LIST.);

run;

Re: Choosing variables based on condition

KiranMaddi — Thu, 04 Jun 2015 10:22:46 GMT

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

Re: Choosing variables based on condition

RW9 — Thu, 04 Jun 2015 11:59:03 GMT

SAS automatically keeps metadata about tables. These can be seen in SASHELP library, under VTABLE and VCOLUMN. You can also reference these in SQL as dictionary.tables, and dictionary.columns. What I am doing in that statement is looking at what columns are in your dataset. If the name is NAME, AGE, SEX, or has BMW at any point in the variable name, then create a macro variable with these column names. Then the datastep uses this macro variable to know what columns to keep.

Re: Choosing variables based on condition

Ksharp — Thu, 04 Jun 2015 12:05:08 GMT

If you have multiple KAY word need to grab ,then use prxmatch().

Re: Choosing variables based on condition

JackHamilton — Thu, 04 Jun 2015 13:16:51 GMT

Why not simply

set have (keep=name--sex bmw:)

Re: Choosing variables based on condition

KiranMaddi — Thu, 04 Jun 2015 13:49:25 GMT

Hi RW9,

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

Re: Choosing variables based on condition

KiranMaddi — Thu, 04 Jun 2015 13:54:13 GMT

Hi Xia,

I did not understand that. Please can you be more specific?

Re: Choosing variables based on condition

RW9 — Thu, 04 Jun 2015 13:56:57 GMT

Well, just update the where and/or the let:

%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);

However JackHamilton raises a great point, if your variables do have the same prefix, then you can use that. I always forget about this syntax. So try that first, it will shrink the needed code.

Re: Choosing variables based on condition

Ksharp — Thu, 04 Jun 2015 14:00:40 GMT

If they starts with bmw , Mercedes ,then use JACK suggested:

set have (keep=name--sex bmw: Mercedes: );

Otherwise, could consider use the following:

(upcase(NAME) in ("NAME","AGE","SEX") or prxmatch('/bmw|Mercedes|Porche/i',name);

Re: Choosing variables based on condition

KiranMaddi — Thu, 04 Jun 2015 14:20:27 GMT

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?

Re: Choosing variables based on condition

KiranMaddi — Thu, 04 Jun 2015 14:22:03 GMT

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);

Re: Choosing variables based on condition

RW9 — Thu, 04 Jun 2015 14:35:48 GMT

You may want to clarify your initial post. By create a prompt, what software are you using? Is it stored process or something?

My suggestion would be to change the layout of your dataset to match the functionality you require, so instead of:

Name Age sex BMW BMW_Premium BMW_Standard AUDI Mercedes Mercedes_card Vauxhall Toyota Toyota_Rnw

x x x x x x x x x x x x

Normalise the data:

NAME AGE SEX BRAND BRAND_SUBCATEGORY RESULT

XYZ 123 M BMW 1

XYZ 123 M BMW Premium 12

ZYT 10 F Mercedes ...

This will make your life easier in the long run. You can get a distinct list of BRANDS by select distinct(BRAND) from..., and you can where clauses as many or as few as you need to return, e.g. where brand in (-yourlist-).

If your output needs to be transposed then:

proc sort data=have out=want;

by name age sex brand; brand_subcategory;

run;

proc tranpose

by name age sex brand; brand_subcategory;

var result;

run;

Then your columns will be named col1-colx, for all the subsets you choose.

Re: Choosing variables based on condition

KiranMaddi — Thu, 04 Jun 2015 15:00:38 GMT

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.

Re: Choosing variables based on condition

Ron_MacroMaven — Thu, 04 Jun 2015 15:04:44 GMT

Here is a follow-up on Jack's suggestion about keep=()

I discovered this trick recently in polishing summary parameters

%let keep_ids = name age sex;

%let list_brands = bmw mercedes;

*note double space, therefore need for cmpres;

%let keep_brands = %sysfunc(tranwrd(%cmpres(&list_brands)%str( )

,%str( ),%str(: )));

%put &=keep_brands;

set have( keep = &keep_ids &keep_brands);

Re: Choosing variables based on condition

KiranMaddi — Thu, 04 Jun 2015 15:10:15 GMT

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?

Re: Choosing variables based on condition

KiranMaddi — Fri, 05 Jun 2015 09:14:52 GMT

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;

Re: Choosing variables based on condition

RW9 — Fri, 05 Jun 2015 09:27:52 GMT

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;

Re: Choosing variables based on condition

KiranMaddi — Fri, 05 Jun 2015 10:36:01 GMT

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

Re: Choosing variables based on condition

andreas_lds — Fri, 05 Jun 2015 12:33:35 GMT

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;