topic Re: Using Proc SQL, how to determine which column to use in where clause based on user value entered in SAS Programming

Using Proc SQL, how to determine which column to use in where clause based on user value entered?

ncsthbell — Mon, 15 Feb 2016 18:05:32 GMT

I have defined one prompt named ‘whichcolumn’. Based on the length of the value the user enters into the prompt I need to determine which column to use in my ‘where’ clause.

Is this possible to build as part of a where clause? If not, are there any other suggestions as to how I can accomplish this?

My table columns:

Column2 Column2Desc Column3 Column3Desc Column4 Column4Desc

Example:

If the user enters ‘72’, the length is 2 so I want to use Column2 in my where clause.

If the user enters ‘721’, the length is 3 so I want to use Column3 in my where clause.

If the user enters ‘7211’, the length is 4 so I want to use Column4 in my where clause.

Thanks for any assistance!

Re: Using Proc SQL, how to determine which column to use in where clause based on user value entered

Haikuo — Mon, 15 Feb 2016 18:22:06 GMT

Since you mentioned 'prompt', this question maybe more suitable for Enterprise Guide sub-forum, where you are more likely to get help from EG experts. Something along the line of following simple implementation may work for you, try it in your stored process.


%let var=column%length(&seq); /*assume seq is your prompt name*/

proc sql;
select blah blah from blah where &var blah
;
quit;

Of course you can use it as a compound one step in your Proc SQL, I was trying to set up the flow chart for you, in case you need more flexibility.

Re: Using Proc SQL, how to determine which column to use in where clause based on user value entered

ncsthbell — Thu, 25 Feb 2016 19:40:20 GMT

Thanks so much for your assistance!