Help using Base SAS procedures

proc sql

Reply
Contributor
Posts: 57

proc sql

Hi

I have 12 character variables named proc1, proc2, proc3, proc4, ..., proc12.

I would like to check for a value in all the 12 variables using proc sql.

I used this code.

proc sql noprint;

create table temp6 as select proc1-proc12
from temp3
where proc1-proc12 eqT 'ABC';
quit;

I got this error.

ERROR: Expression using subtraction (-) requires numeric types.
ERROR: Expression using subtraction (-) requires numeric types.
ERROR: Expression using equals (=) has components that are of different data types.

SAS assumes the hyphen as a minus sign.

I want to take the array route if all else fails.

Thanks in advance,
Sandhya.
Valued Guide
Posts: 2,175

Re: proc sql

Sandhya

> I would like to check for a value in all the 12
> variables using proc sql.

> where proc1-proc12 eqT 'ABC';


eqT operator compares only the shorter of the two string lengths

? (i.e. the contains) operator seems more likely to help, like
where catx('/', of proc1 - proc12) ? 'ABC'
however, I'm not sure sql tolerates variable lists like "of proc1-proc12"
So, use a data step.
Can you use a data step for this?
If not, why not?

peterC too eager
Message was edited by: Peter.C
Contributor
Posts: 57

Re: proc sql

Yes I can use the data steps

Thanks,
Sandhya.
Valued Guide
Posts: 2,175

Re: proc sql

unfortunately, the variable list notation [pre] of proc1 - proc12 [/pre]is not accepted in the where handling of data steps either, but the quick work around goes like (let sql fill a comma-separated list of these variables)[pre]%let table = your.dataset ;
%let var_prefix = proc ;
%let var_list = ;
proc contents data=&table noprint out=_data_ ; run ;
proc sql noprint ;
select name into :var_list separated by ', '
from &syslast
where type = 2 /* strings */
and upcase(name) eqt "%upcase(&var_prefix)"
;
quit ;
proc sql ;[/pre] your query goes here followed by [pre] where catx( '/', &var_list ) ? 'ABC' )
Super User
Posts: 9,687

Re: proc sql

Hi.
You nearly get the target!
Just to use hard code.
Such as

proc sql noprint;

create table temp6 as select proc1,proc2,......proc12
from temp3
where proc1 eq 'ABC' or proc2 eq 'ABC' or proc3 eq 'ABC' or proc4 eq 'ABC' ........;
quit;
Contributor
Posts: 57

Re: proc sql

Thank you for your suggestions.

It was very helpful.

Thanks,
Sandhya.
Ask a Question
Discussion stats
  • 5 replies
  • 916 views
  • 0 likes
  • 3 in conversation