DATA Step, Macro, Functions and more

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

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 82
Accepted Solution

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

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!


Accepted Solutions
Solution
‎02-25-2016 02:39 PM
Respected Advisor
Posts: 3,124

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

[ Edited ]

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. 

View solution in original post


All Replies
Solution
‎02-25-2016 02:39 PM
Respected Advisor
Posts: 3,124

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

[ Edited ]

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. 

Frequent Contributor
Posts: 82

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

Thanks so much for your assistance!

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 2 replies
  • 181 views
  • 4 likes
  • 2 in conversation