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;
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?
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?
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;
Hmm it's still showing as hhh-123456789; is the string correct for when you want to remove the first 4 from a character?
@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?
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
Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.
What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.
Find more tutorials on the SAS Users YouTube channel.
Ready to level-up your skills? Choose your own adventure.