Hi. In the code below I'm reading in a Proc Sql query using the DATALINES statement to a variable named USER_SQL. I'm then trying to update the USER_SQL variable to put '/*' and '*/' around the Create Table statement. Basically I'm trying to comment out the Create Table statement.
I'm having two problems in the second datastep below:
1. CRT_TBL_STTMNT = SUBSTR(USER_SQL, INDEX(USER_SQL,'CREATE TABLE'), INDEX(USER_SQL,' AS')+2);
This statement is extracting too many characters. I want it to pull just 'CREATE TABLE OUT.MYTAB AS', but instead it's pulling 'CREATE TABLE OUT.MYTAB AS SELECT DISTINCT'. I can't figure out why.
2. USER_SQL = TRANWRD(USER_SQL, CRT_TBL_STTMNT, NEW_CRT_TBL_STTMNT);
I'm trying to replace the current Create Table statement inside the USER_SQL variable with the new Create Table statement that has the Comment signs around it, but it's failing to make the replacement.
If you have time to take a look the code you can run is:
DATA USER_SQL;
INFILE DATALINES EOF=WRITE;
LENGTH USER_SQL $32767 ;
RETAIN USER_SQL;
INPUT;
USER_SQL=CATX(' ',USER_SQL,_INFILE_);
RETURN;
WRITE: OUTPUT;
DATALINES4;
PROC SQL;
CREATE TABLE OUT.MYTAB AS
SELECT DISTINCT
CLM.BENE_SK
,1 as card
FROM OPI_AAL.CLM AS CLM
INNER JOIN NFPP_TRD.&SYSUSERID._HHASWITCH_CLMS AS HHACLMS
ON CLM.BENE_SK = HHACLMS.BENE_SK
WHERE (CLM.CLM_FROM_DT BETWEEN &bgn_dt AND &end_dt)
AND (CLM.CLM_PRNCPL_DGNS_CD LIKE 'I50%' or CLM.CLM_DGNS_1_CD LIKE 'I50%' or CLM.CLM_DGNS_2_CD LIKE 'I50%'
or CLM.CLM_DGNS_3_CD LIKE 'I50%' or CLM.CLM_DGNS_4_CD LIKE 'I50%' or CLM.CLM_DGNS_5_CD LIKE 'I50%'
or CLM.CLM_DGNS_6_CD LIKE 'I50%' or CLM.CLM_DGNS_7_CD LIKE 'I50%' or CLM.CLM_DGNS_8_CD LIKE 'I50%')
AND CLM.CLM_FINL_ACTN_IND = 'Y'
AND CLM.CLM_TYPE_CD BETWEEN 2000 AND 2999
order by BENE_SK
;QUIT;
;;;;
DATA USER_SQL2;
SET USER_SQL;
IF FIND(USER_SQL,'CREATE TABLE') = 0 THEN CRT_TBL_IND=0; ELSE CRT_TBL_IND=1;
IF CRT_TBL_IND=1 THEN DO;
/* PULL OUT THE CREATE TABLE STATEMENT */
CRT_TBL_STTMNT = SUBSTR(USER_SQL, INDEX(USER_SQL,'CREATE TABLE'), INDEX(USER_SQL,' AS')+2);
/* APPEND SAS COMMENT MARKERS AROUND THE CREATE TABLE STATEMENT */
NEW_CRT_TBL_STTMNT = CATX(' ', '/* ',SUBSTR(USER_SQL,INDEX(USER_SQL,'CREATE TABLE'),INDEX(USER_SQL,' AS')+2),' */');
/* REPLACE THE EXISTING CREATE TABLE STATEMENT WITH THE COMMENTED VERSION */
USER_SQL = TRANWRD(USER_SQL, CRT_TBL_STTMNT, NEW_CRT_TBL_STTMNT);
END;
RUN;
... View more
