topic need help to modify the records in a variable. in SAS Procedures

need help to modify the records in a variable.

npatel2302 — Wed, 10 Aug 2016 15:12:34 GMT

I have a dataset variable with a sql query in it, and i want to put the words in the parantheses into quotations to read that query from SAS.Not sure how can i do that. Can any one please help?

Example-

select t1.star, t2.movies,t1.origin

from (select movies, origin from movie_list where origin=(USA)) t2

left outer join star_names t1

on t1.origin=t2.origin

(where t1.star_first_name=(leo) and t1.star_first_name=(brad))

;

I want to put USA, leo and brad into single quotations like -('USA') ('leo') ('brad')

Need help to modify the records in a variable

npatel2302 — Wed, 10 Aug 2016 15:26:01 GMT

I have a dataset variable with a sql query in it, and i want to put the words in the parantheses into quotations to read that query from SAS.Not sure how can i do that. Can any one please help?

Example-

select t1.star, t2.movies,t1.origin

from (select movies, origin from movie_list where origin=(USA)) t2

left outer join star_names t1

on t1.origin=t2.origin

(where t1.star_first_name=(leo) and t1.star_first_name=(brad))

;

I want to put USA, leo and brad into single quotations like -('USA') ('leo') ('brad')

Re: Need help to modify the records in a variable

ballardw — Wed, 10 Aug 2016 15:34:49 GMT

The easiest way to get imbedded quotes is to use a different quote.

You do not describe how you created the data set in the first place, which would probably be the best place to do this.

Assuming you had something like

x='select t1.star, t2.movies,t1.origin
from (select movies, origin from movie_list where origin=(USA)) t2
left outer join star_names t1
on t1.origin=t2.origin
(where t1.star_first_name=(leo) and t1.star_first_name=(brad))
;
'  ;

Then something like this

x='select t1.star, t2.movies,t1.origin
from (select movies, origin from movie_list where origin=("USA")) t2
left outer join star_names t1
on t1.origin=t2.origin
(where t1.star_first_name=("leo") and t1.star_first_name=("brad"))
;
'  ;

Note the use of the " between the ' ' that define the long string value.

We may need more details on how you are building you dataset if you are not using simple assignments.

Re: Need help to modify the records in a variable

npatel2302 — Wed, 10 Aug 2016 15:40:46 GMT

The data is coming from one of the sql server table. Variable name is query_text which basically has the list of query associated with the claims processing. The output that you mentioned will not work as i will have run the whole query to SQL sever again using will be using macro for that, and double quotes might not work.

Hope that helps

Re: Need help to modify the records in a variable

Reeza — Wed, 10 Aug 2016 19:24:02 GMT

What do you want as a final result?

I'm not seeing why you can't just type single quotes into your query at the moment...

Re: Need help to modify the records in a variable

Reeza — Wed, 10 Aug 2016 19:24:39 GMT

This will also never evaluate to true:

(where t1.star_first_name=(leo) and t1.star_first_name=(brad))

Re: Need help to modify the records in a variable

npatel2302 — Wed, 10 Aug 2016 20:30:28 GMT

Thanks for help Reeeza.

So in brief, i have a dataset with obs_number, query_text and application id, and that data is coming from one SQL server table.

Example of Query_text -

Select * from Table1 t1

left outer join Table2 t2

( t1.CASE_STATUS_TYPE_CD = (NEW) OR t1.CASE_STATUS_TYPE_CD = (OPEN) ) AND ( t3.SEARCH_VALUE_NAME = (HighVal_Ind) AND t3.SEARCH_VALUE_NUM <= 3 ) AND ( t4.SEARCH_VALUE_NAME = (Curr_Strategy) AND t4.SEARCH_VALUE_STRG = (095) ) GROUP BY t1.CASE_REFERENCE_NUM,t2.LAST_SCRFP_FRD_CARD,t1.SERVICE_XID ORDER BY t2.LAST_SCRFP_FRD_CARD DESC

What i need to do in sas is to put quotations in to the character records as below

Select * from Table1 t1

left outer join Table2 t2

( t1.CASE_STATUS_TYPE_CD = (NEW) OR t1.CASE_STATUS_TYPE_CD = ('OPEN') ) AND ( t3.SEARCH_VALUE_NAME = ('HighVal_Ind') AND t3.SEARCH_VALUE_NUM <= 3 ) AND ( t4.SEARCH_VALUE_NAME = ('Curr_Strategy') AND t4.SEARCH_VALUE_STRG = ('095') ) GROUP BY t1.CASE_REFERENCE_NUM,t2.LAST_SCRFP_FRD_CARD,t1.SERVICE_XID ORDER BY t2.LAST_SCRFP_FRD_CARD DESC

Hope that clarifies my question.

Thanks a lot.