select where any 'variable' = 'Y'

Reply
Valued Guide
Posts: 860

select where any 'variable' = 'Y'

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?

Super Contributor
Posts: 1,636

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

Posted in reply to Steelers_In_DC

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

Super User
Posts: 5,518

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

Posted in reply to Steelers_In_DC

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.

Respected Advisor
Posts: 4,935

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

Posted in reply to Steelers_In_DC

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;

PG

PG
Valued Guide
Posts: 765

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

Posted in reply to Steelers_In_DC

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

Ask a Question
Discussion stats
  • 4 replies
  • 275 views
  • 0 likes
  • 5 in conversation