BookmarkSubscribeRSS Feed
Sandhya
Fluorite | Level 6
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.
5 REPLIES 5
Peter_C
Rhodochrosite | Level 12
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
Sandhya
Fluorite | Level 6
Yes I can use the data steps

Thanks,
Sandhya.
Peter_C
Rhodochrosite | Level 12
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' )
Ksharp
Super User
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;
Sandhya
Fluorite | Level 6
Thank you for your suggestions.

It was very helpful.

Thanks,
Sandhya.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 5 replies
  • 2693 views
  • 0 likes
  • 3 in conversation