@yabwon Perfect.Your code is working fine. I have two more questions before I close this topic.   - why there is a two single quote after the string 'CLOSING RUN. Have you noticed it?   49 %put rep_run=%superq(rep_run); rep_run='CLOSING RUN'' INSURANCE','INSURANCE','SECONDARY CLOSING RUN INSURANCE','BUSINESS INSURANCE','RI_BCT' - Can we accomplish this task only by data step? any alternate methods? ... View more

@yabwon How do you handle your second data step If I want to create multiple macro variables in the first data step as shown below?   data _null_; call symputx("rep_run", "closing run' insurance,insurance,secondary closing run insurance,business insurance,RI_BCT"); call symputx("chk_run", "insurance,secondary closing run insurance,business insurance,PI_BCT"); call symputx("chk_date_sql", "RI_TCT"); run; ... View more

@japelin your code is working fine with the SAS datasets but not with the SQL tables. I'm getting the similar error as I mentioned before.   217 'CLOSING RUN(VFA)','SECONDARY CLOSING RUN,CLOSING RUN(BFA)','IMPACT OF OVERRETURN ON CPM,'BASELINE FOR INITAL ___ ___ ___ ___ 49 49 49 49 My program is similar as below, but the source table is from SQL.   Proc Sql ; Select rep_run ,(Case When (rep_run) in (&rep_run.) then 'PI_PPT' else ' ' end ) as x from sqltbl.test; ... View more

Ok. Can I call the macro variable in proc SQL connect to SQL server without any double quotes around? ... View more

@Kurt_Bremser I want to use the macro variable value to use in SQL Server tables to check for values. As SQL server will consider only single quote for the string, how to tackle it now?   I would like to know how to handle case when here when you want to compare the macro variable values with SQL server field values?   Proc Sql noprint; Connect to SQLSERVER (DATAsrc=&DATASRC. AUTHDOMAIN="XXXXX." dbMax_text=32767); Execute ( Insert into Meta_cntrl Select Distinct NULL as org_id Case When (rep_run) in (&rep_run.) then 'PI_PPT' else ' ' end as reporting_value; ) by SQLSERVER; ; Disconnect from SQLSERVER; quit; ... View more

@japelin I just ran your code. Is this not an error?   Log:   26 ! data _null_; 27 call symput('rep_run',upcase("'closing run' insurance','insurance','secondary closing run insurance','business 27 ! insurance','RI_BCT'")); 28 run; NOTE: DATA statement used (Total process time): real time 0.00 seconds cpu time 0.00 seconds 29 30 %put ####&rep_run.; NOTE: Line generated by the macro variable "REP_RUN". 30 'CLOSING RUN' INSURANCE','INSURANCE','SECONDARY CLOSING RUN INSURANCE','BUSINESS INSURANCE','RI_BCT' ___ ___ ___ 49 49 49 ... View more

@Tom If I run the code below, I could see a some blanks in the value of macro variable rprun_pi_val. How to remove the blanks?   %let rprun_pi=main close run(VFB),secondary close run,main close run(BBB),impact of overreturn on CSP,baseline for inital recognition(VFB), Coverage Units new business,main close run mortality,main close run longevity,main close run lapse down,secondary close run mortality, secondary close run longevity,secondary close run lapse down; %let rprun_pi_val=%upcase(%unquote(%str(%')%qsysfunc(tranwrd(%superq(rprun_pi),%str(,),','))%str(%'))); %put &=rprun_pi_val; Results:   26 %let rprun_pi=main close run(VFB),secondary close run,main close run(BBB),impact of overreturn on CSP,baseline for inital 26 ! recognition(VFB), Coverage Units new business,main close run mortality,main close run longevity,main 26 ! close run lapse down,secondary close run mortality, 27 secondary close run longevity,secondary close run lapse down; 28 %let rprun_pi_val=%upcase(%unquote(%str(%')%qsysfunc(tranwrd(%superq(rprun_pi),%str(,),','))%str(%'))); 29 %put &=rprun_pi_val; RPRUN_PI_VAL='MAIN CLOSE RUN(VFB)','SECONDARY CLOSE RUN','MAIN CLOSE RUN(BBB)','IMPACT OF OVERRETURN ON CSP','BASELINE FOR INITAL RECOGNITION(VFB)',' COVERAGE UNITS NEW BUSINESS','MAIN CLOSE RUN MORTALITY','MAIN CLOSE RUN LONGEVITY','MAIN CLOSE RUN LAPSE DOWN','SECONDARY CLOSE RUN MORTALITY',' SECONDARY CLOSE RUN LONGEVITY','SECONDARY CLOSE RUN LAPSE DOWN'   Desired results:   26 %let rprun_pi=main close run(VFB),secondary close run,main close run(BBB),impact of overreturn on CSP,baseline for inital 26 ! recognition(VFB), Coverage Units new business,main close run mortality,main close run longevity,main 26 ! close run lapse down,secondary close run mortality, 27 secondary close run longevity,secondary close run lapse down; 28 %let rprun_pi_val=%upcase(%unquote(%str(%')%qsysfunc(tranwrd(%superq(rprun_pi),%str(,),','))%str(%'))); 29 %put &=rprun_pi_val; RPRUN_PI_VAL='MAIN CLOSE RUN(VFB)','SECONDARY CLOSE RUN','MAIN CLOSE RUN(BBB)','IMPACT OF OVERRETURN ON CSP','BASELINE FOR INITAL RECOGNITION(VFB)','COVERAGE UNITS NEW BUSINESS','MAIN CLOSE RUN MORTALITY','MAIN CLOSE RUN LONGEVITY','MAIN CLOSE RUN LAPSE DOWN','SECONDARY CLOSE RUN MORTALITY','SECONDARY CLOSE RUN LONGEVITY','SECONDARY CLOSE RUN LAPSE DOWN' ... View more

@Tom @PaigeMiller Great! I do have one more question. How to convert the strings to upper case after enclosing it with single quote.   Desired result is,   'CLOSING RUN INSURANCE','INSURANCE','SECONDARY CLOSING RUN INSURANCE','BUSINESS INSURANCE','RI_BCT' ... View more

I have already tried with B8601dz. and E8601dz formats. It's not producing the desired results. ... View more