topic Re: select where any 'variable' = 'Y' in SAS Programming

select where any 'variable' = 'Y'

Steelers_In_DC — Tue, 11 Sep 2012 19:50:05 GMT

I have a dataset with a large amount of variables. I would like to get a count of variables that = 'Y' over a set time frame, in this case 6 months. Is there anyway to go through without selecting each variable

when a = 'Y' or b = 'Y' or c = 'Y'

I'd like to select:

select * from 'location' where date = 'date' and

"any variable" = 'Y'

Is this possible?

Re: select where any 'variable' = 'Y'

Linlin — Tue, 11 Sep 2012 20:02:41 GMT

Hi,

You can use array in a datastep:

data have;

input (location a b e d e f g) (:$1.);

cards;

e 1 2 c d y u p

e x d f f f v v

;

data want;

set have(where=(location='e'));

array _c a--g;

do over _c;

if upcase(_c)='Y' then do; output;leave;end;end;

run;

proc print;run;

Go Patriots

Re: select where any 'variable' = 'Y'

Astounding — Tue, 11 Sep 2012 20:10:34 GMT

If you really need counts, PROC FREQ would be appropriate:

proc format;

value $Y ' '=blank 'y', 'Y'='Y' other='non-Y';

run;

proc freq data=have;

tables _character_ / missing;

format _character_ $Y.;

where (apply any subsetting needed);

run;

Good luck.

Re: select where any 'variable' = 'Y'

PGStats — Tue, 11 Sep 2012 20:13:55 GMT

You can't use variable lists in SQL, so better use a data step :

data want;

set location;

if findc(cats(of _character_),'Yy') > 0;

run;

Re: select where any 'variable' = 'Y'

MikeZdeb — Tue, 11 Sep 2012 20:14:49 GMT

hi ... if it's always uppercase ...

data have;

input (location a b e d e f g) (:$1.);

cards;

E 1 2 C D Y U P

E X D F F F V V

;

data want;

set have;

if whichc('Y',of _character_);

run;

if it's select if either upper or lower case ...

data have;

input (location a b e d e f g) (:$1.);

cards;

E 1 2 C D Y U P

E X D F F F V V

y A 1 2 3 4 5 Z

;

data want;

set have;

if whichc('Y',of _character_) or whichc('y', of _character_);

run;

if it's counts, use PROC FREQ as suggested by astounding