Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Home
- /
- Programming
- /
- Programming
- /
- Re: SUM OF in WHERE

Options

- RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page

🔒 This topic is **solved** and **locked**.
Need further help from the community? Please
sign in and ask a **new** question.

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

Posted 08-07-2019 05:19 AM
(641 views)

Is it impossible to use SUM OF in WHERE? For example, suppose six dummy variables as follows.

```
data have;
do i=1 to 30;
a1=ranbin(1,1,0.5);
a2=ranbin(1,1,0.5);
a3=ranbin(1,1,0.5);
b1=ranbin(1,1,0.5);
b2=ranbin(1,1,0.5);
b3=ranbin(1,1,0.5);
output;
end;
run;
```

The following successfully subsets by IF with SUM OF.

```
data usual;
set have;
if sum(of a: b:)=3;
run;
```

However, it seems WHERE does not allow SUM OF, while allows SUM per se.

```
data want;
*set have(where=(sum(of a: b:)=3));
set have;
*where sum(of a: b:)=3;
*where sum(of a1--b3)=3;
where sum(a1,a2,a3,b1,b2,b3)=3;
run;
```

Should I always list all the variables to use WHERE? There are too many variables to be specified. Thanks.

1 ACCEPTED SOLUTION

Accepted Solutions

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

Subsetting if uses pure data step syntax, "where" uses SQL syntax, where the "of" construct is not available.

You can automate it by retrieving the variable list from dictionary.columns into a macro variable:

```
proc sql noprint;
select name into :varlist separated by ',' from dictionary.columns
where
libname = 'WORK' and memname = 'HAVE' and
upcase(substr(name,1,1)) in ('A','B')
;
quit;
data want;
set have;
where sum(&varlist.) = 3;
run;
```

3 REPLIES 3

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

Subsetting if uses pure data step syntax, "where" uses SQL syntax, where the "of" construct is not available.

You can automate it by retrieving the variable list from dictionary.columns into a macro variable:

```
proc sql noprint;
select name into :varlist separated by ',' from dictionary.columns
where
libname = 'WORK' and memname = 'HAVE' and
upcase(substr(name,1,1)) in ('A','B')
;
quit;
data want;
set have;
where sum(&varlist.) = 3;
run;
```

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

Thanks for the considerate details. I didn't know whether WHERE uses SQL to subset.

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. **Registration is now open through August 30th**. Visit the SAS Hackathon homepage.

How to Concatenate Values

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.