topic Re: proc sql in SAS Procedures

proc sql

Sandhya — Thu, 02 Dec 2010 14:46:54 GMT

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.

Re: proc sql

Peter_C — Thu, 02 Dec 2010 15:00:26 GMT

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

Re: proc sql

Sandhya — Thu, 02 Dec 2010 15:02:35 GMT

Yes I can use the data steps

Thanks,
Sandhya.

Re: proc sql

Peter_C — Thu, 02 Dec 2010 15:23:55 GMT

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

Re: proc sql

Ksharp — Fri, 03 Dec 2010 03:43:35 GMT

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;

Re: proc sql

Sandhya — Mon, 06 Dec 2010 15:48:31 GMT

Thank you for your suggestions.

It was very helpful.

Thanks,
Sandhya.