extracting a single character inside a SQL proc? is substring the way?

[cieffegi]

I have this sql proc:

proc sql;
create table last_positions as select distinct
     a.*,
     b.RATING AS numerical_rating

from last_positions_temp as a
left join &LIB_INP..closed_positions as b
on a.NDG = b.NDG and a.extr_dt = b.extr_dt
quit;

Now, this is the problem:
the "RATING" table contains two digit value like "R1", "R2", "R3" up to "R9".
I want to keep only the numerical part.
I have tried several sintax like

 substring(b.RATING,2,1) AS numerical_rating
or
substr (b.RATING,2,1) AS numerical_rating
or b.RATING AS numerical_rating = substring(b.RATING,2,1) AS numerical_rating
and also other combinations.

Every time i get this error message:

551 SUBSTRING(b.RATING,2,1) AS stato_dr
-
22
76
ERROR 22-322: Syntax error, expecting one of the following: !, !!, &, *, **, +, -, /, <, <=,
<>, =, >, >=, ?, AND, BETWEEN, CONTAINS, EQ, EQT, FROM, GE, GET, GT, GTT, IN,
IS, LE, LET, LIKE, LT, LTT, NE, NET, NOT, NOTIN, OR, ^, ^=, |, ||, ~, ~=.

ERROR 76-322: Syntax error, statement will be ignored.

NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.
NOTE: Remote submit to SERV complete.
NOTE: Remote submit to SERV commencing.
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE SQL used (Total process time):
real time 47.34 seconds
cpu time 0.00 seconds

is it possible to acheive the desired result inside the proc sql or should i use a run around of some sort?
Thank you

[Amir]

Hi @cieffegi,

Thanks for sharing the code and error message.

It looks to me like there might be something missing before the substring() function is used.

Please share the log showing the whole proc sql step, using the "</>" to post the details, so that we can see the whole context.

Thanks & kind regards,

Amir.

[cieffegi]

this is the entire program i'm trying to use:

filename pgm_auto '/crediti/MOTORI/01_COMUNE/autoexec/auto_batch_noabend.sas';
%inc pgm_auto;
/*%write_log (name=imamac_xx_datamart_proposte, motore = 01_COMUNE);*/

data _null_;
 TEMPO = put(time(),time8.);
 call symput ('TEMPO',TEMPO);
run;

%put /===================================================================/;
%put |   PGM: engine_XX_DATAMART_PROPOSTE   - START : &TEMPO.               |;
%put /===================================================================/;


/*Definizione parametri librerie e date*/

%LET LIB_INP=PALL_INP;
%LET LIB_DMT=PALL_DTM;

/*intercetto l'ultima proposta per ogni cdg*/
proc summary data = &LIB_INP..closed_positions missing nway;
class NDG; 
var EXTR_DT;
output out = last_positions_temp max=;
run;


proc sql;
 create table last_positions as select distinct 
  a.*,
  substring(b.RATING,2,1)  AS numerical_rating
   
  from last_positions_temp as a
left join &LIB_INP..closed_positions as b
on a.NDG = b.NDG and a.EXTR_DT = b.EXTR_DT
quit;

and this is the log i get from it

NOTE: Ambiente inizializzato correttamente

NOTE: DATA statement used (Total process time):
      real time           0.00 seconds
      cpu time            0.01 seconds


/===================================================================/
|   PGM: engine_XX_DATAMART_PROPOSTE   - START : 14:46:18               |
/===================================================================/

NOTE: There were 632085 observations read from the data set PALL_INP.closed_positions.
NOTE: The data set WORK.last_positions_temp has 221755 observations and 5 variables.
NOTE: Compressing data set WORK.last_positions_temp increased size by 27.94 percent.
      Compressed is 174 pages; un-compressed would require 136 pages.
NOTE: PROCEDURE SUMMARY used (Total process time):
      real time           1.26 seconds
      cpu time            2.41 seconds


334     substring(b.RATING,2,1)  AS numerical_rating
                                             -
                                             22
                                             76
ERROR 22-322: Syntax error, expecting one of the following: !, !!, &, *, **, +, -, /, <, <=,
              <>, =, >, >=, ?, AND, BETWEEN, CONTAINS, EQ, EQT, FROM, GE, GET, GT, GTT, IN,
              IS, LE, LET, LIKE, LT, LTT, NE, NET, NOT, NOTIN, OR, ^, ^=, |, ||, ~, ~=.

ERROR 76-322: Syntax error, statement will be ignored.

NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.
NOTE: Remote submit to SERV complete.

[Amir]

Hi @cieffegi,

Thanks for the info.

Have you tried substr in stead of substring, as per the documentation:

https://documentation.sas.com/doc/en/pgmsascdc/v_006/lefunctionsref/p0uev77ebdwy90n1rsd7hwjd2qc3.htm

Kind regards,

Amir.

[cieffegi]

it is the first thing i have tried.
Unfortunately, it doesn't work.

[Amir · Accepted solution]

There appears to be a semi colon missing (";") before the quit for the proc sql.

I don't think it would cause the problem, but it should be there.

Please try adding it and see what happens.

[cieffegi]

you won't beleive, that was all the issue!
Now it works,

thank you!

[Amir]

Hi @cieffegi, glad to hear it is working and thanks for marking a post as the solution.

I was going to suggest using validate which "Checks the accuracy of a query expression's syntax and semantics without executing the expression" to see if that gave any clues, and when I tried it after substituting sashelp table names in place of yours:

proc sql;
/*  create table last_positions as  */
 	validate
 	select distinct 
 	a.*,
 	substr(b.name,2,1)  AS numerical_rating
   
 	from sashelp.cars as a
	left join sashelp.class as b
	on a.make = b.name and a.cylinders = b.age
	;
quit;

no issues were reported:

73         proc sql;
 74         /*  create table last_positions as  */
 75          validate
 76          select distinct
 77          a.*,
 78          substr(b.name,2,1)  AS numerical_rating
 79         
 80          from sashelp.cars as a
 81         left join sashelp.class as b
 82         on a.make = b.name and a.cylinders = b.age
 83         ;
 NOTE: PROC SQL statement has valid syntax.
 84         quit;

So you might find using it useful in future.

Thanks & kind regards,

Amir.

[Tom]

Looks like there is some invisible character near the underscore in the name you are trying to give the new variable.

Try re-typing that line of the program.