SUBSTR field and JOIN

[bhca60]

Need help with the following:

tableA has c_key that looks like hhh-123456789 where tableB has cid like 123456789. I need to remove the first four (hhh-) from the c_key in tableA so that I can join on cid in tableB. I did a substr but I am getting the following error:

proc sql;
     create tableC
        select a.*,
               b.pl_svc as svc,
               b.grp_cd as grp,
	       substr('c_key',1,5) AS a.cid
                                       -
                                       22
                                       76
ERROR 22-322: Syntax error, expecting one of the following: a quoted string, ',', AS, FORMAT, FROM, INFORMAT, INTO, LABEL, LEN, 
              LENGTH, TRANSCODE.  
ERROR 76-322: Syntax error, statement will be ignored.

           from tableA a
              LEFT JOIN tableB as b
                 on a.cid = b.cid

    
quit;

[PaigeMiller · Accepted solution]

From now on, when you have an error in a PROC, show us the log for that PROC. We need to see the code as it appears in the log, and any messages such as errors, warnings and noes. DO NOT show us just the error messages in the log, detached from the code in the log.

substr('c_key',1,5) AS a.cid

a.cid is not a valid variable name in SAS. You can only use letters, numbers and underscores in variable names. You cannot use a dot in variable names.

Although that code seems suspicious to me because substr('c_key',1,5) is a constant with value 'c_key', why would you do that?

[ballardw]

Actually has nothing to do with the Join, Substr but the name of the variable. When you say A.CID you should be addressing a variable CID that exists in the data set with the alias name of A. You are selecting into a new table so you don't use the alias as the result, barring other syntax issues, will be the name of the variable in the newly created data set.

Example of wrong and right:

689  proc sql;
690     create table junk as
691     select substr(a.name,1,2) as a.name
                                      -
                                      22
                                      76
ERROR 22-322: Syntax error, expecting one of the following: a quoted string, ',', AS, FORMAT,
              FROM, INFORMAT, INTO, LABEL, LEN, LENGTH, TRANSCODE.

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

692     from sashelp.class as a
693     ;
694  quit;
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds



695  proc sql;
696     create table junk as
697     select substr(a.name,1,2) as name
698     from sashelp.class as a
699     ;
NOTE: Table USER.JUNK created, with 19 rows and 1 columns.

700  quit;
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.01 seconds
      cpu time            0.00 seconds

---

@bhca60 wrote:

Need help with the following:

tableA has c_key that looks like hhh-123456789 where tableB has cid like 123456789. I need to remove the first four (hhh-) from the c_key in tableA so that I can join on cid in tableB. I did a substr but I am getting the following error:

proc sql;
     create tableC
        select a.*,
               b.pl_svc as svc,
               b.grp_cd as grp,
	       substr('c_key',1,5) AS a.cid
                                       -
                                       22
                                       76
ERROR 22-322: Syntax error, expecting one of the following: a quoted string, ',', AS, FORMAT, FROM, INFORMAT, INTO, LABEL, LEN, 
              LENGTH, TRANSCODE.  
ERROR 76-322: Syntax error, statement will be ignored.

           from tableA a
              LEFT JOIN tableB as b
                 on a.cid = b.cid

    
quit;

---

[bhca60]

Hmm it's still showing as hhh-123456789; is the string correct for when you want to remove the first 4 from a character?

[PaigeMiller]

---

@bhca60 wrote:

Hmm it's still showing as hhh-123456789; is the string correct for when you want to remove the first 4 from a character?

---

You use the pronoun "it" and I find this to be completely unclear. What is "it" referring to?

[bhca60]

Need to change hhh-123456789 to 123456789; need to remove hhh-

[jebjur]

To remove the first 4 characters, the syntax for SUBSTR would look like SUBSTR(c_key,5). The 2nd argument determines what position you want to start reading the value, and if there is no 3rd argument, it will read the rest of the value.

17 data a;
18 c_key='hhh-123456789';
19 run;

NOTE: The data set WORK.A has 1 observations and 1 variables.
NOTE: DATA statement used (Total process time):
real time 0.01 seconds
cpu time 0.00 seconds

20
21 data b;
22 set a;
23 new=substr(c_key,5);
24 put c_key= new=;
25 run;

c_key=hhh-123456789 new=123456789

NOTE: There were 1 observations read from the data set WORK.A.
NOTE: The data set WORK.B has 1 observations and 2 variables.
NOTE: DATA statement used (Total process time):
real time 0.01 seconds
cpu time 0.00 seconds

[bhca60]

Thank you; how can I do this in proc sql step ? Because I want to include it in a proc sql table I need to run without creating a separate data set.

proc sql;

substr(a.c_key,5) as cid

from tableA a
left join tableB b
on a.cid=b.cid;
quit;