Char Function Troubles

[buechler66]

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;

[Tom]

How "nice" is your SQL code?

You might have better luck leaving the code broken into individual lines instead of smushing it together into a single string.

Then your searches for PROC SQL and CREATE statements can be much easier (assuming that code is following some minimal formatting standards).

if left(upcase(line)) =: 'PROC SQL' and not findw(line,'noexec',,'sit') then do;
  loc=index(line,';');
  if loc then line=catx(' ',substr(line,1,loc-1),'noexec',substr(line,loc));
  else line=catx(' ',line,'noexec');
end;
if left(upcase(line))=:'CREATE TABLE' then do;
  line=cats('*',line,';');
end;

[buechler66]

Unfortunately it's going to be code from today to code from 10 years ago - and from hundreds of different users to boot.

Do you see any reason why this statement is pulling too many characters? It seems straight forward enough.  I don't get it.

CRT_TBL_STTMNT = SUBSTR(USER_SQL, INDEX(USER_SQL,'CREATE TABLE'), INDEX(USER_SQL,' AS')+2);

[ballardw · Accepted solution]

SUBSTR (target, startcharacter, NUMBEROFCHARACTERS) not position

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 */
   createpos = INDEX(USER_SQL,'CREATE TABLE');
   ASpos =INDEX(USER_SQL,' AS');
	CRT_TBL_STTMNT = SUBSTR(USER_SQL,createpos,(aspos-createpos) +3);
END;
RUN;

[buechler66]

Ugh! Stupid, stupid, stupid. Thank you so much.

[ballardw]

---

@buechler66 wrote:
Ugh! Stupid, stupid, stupid. Thank you so much.

---

I know at least one programming language where there are two forms of substr

substr(string,start position , end position)

substr(string,start position ; length)

the delimiter between the start and end/length tells the function which one to do. WARNING: I may have the examples backwards as far as end position vs length but I think the point is valid.

And if you think that approach may have lead to some code frustration you are absolutely right.

[ChrisNZ]

@ballardw  I wish SAS had this option. Maybe using a fourth parameter.

[ballardw]

---

@ChrisNZ wrote:

@ballardw  I wish SAS had this option. Maybe using a fourth parameter.

---

Maybe time for Ballot box suggestion.

[ChrisNZ]

> Maybe time for Ballot box suggestion.

@ballardw I have proposed countless ideas there! Dozens I think. And not only there, here and here too.

They seem to be gathering dust rather nicely.