Proc SQL Insert Into syntax

ProcLunchbreak — Mon, 20 Aug 2018 19:33:59 GMT

Hello! I have datasets A and B in different formats with similar data that I'm trying to groom into a single dataset called COMBINED. All of the columns but one are working. In table A we have UserIDs with a string length of two, in table B the UserIDs have a string length of 5, but I would like to shorten it to the first two characters. My code at the moment looks like this:

PROC SQL;
CREATE TABLE WORK.COMBINED AS SELECT
/*Other data grooming removed here for brevity*/
  A.Name as Name,
  A.UserID AS UserID
FROM WORK.TableA A ;

INSERT INTO WORK.COMBINED (
/*list of all fields here*/
)
SELECT
/*Other data grooming removed for brevity*/
  B.Name AS Name,
  SUBSTR(B.UserID,1,2) AS UserID
FROM WORK.TableB B ;
QUIT;

I receive the errors

Column UserID could not be found in the table/view identified with the correlation name B.

Unresolved reference to table/correlation name B.

Commenting out this UserID variable, the files combine as expected. Is this not the correct way to reference table B within the substring function? Should I employ a different method in order to take the first two characters of table B's UserID string?

Thanks in advance for any help you can provide.

Re: Proc SQL Insert Into syntax

Kurt_Bremser — Mon, 20 Aug 2018 20:08:40 GMT

Maxim 3: know your data.

What the message says. There is no column userid in tableb.

Re: Proc SQL Insert Into syntax

ProcLunchbreak — Mon, 20 Aug 2018 20:14:39 GMT

For the love of... the column name had a typo in table B.

Sometimes it's staring you in the face and you can't see it.

Thanks Kurt!

topic Re: Proc SQL Insert Into syntax in SAS Procedures

Proc SQL Insert Into syntax

Re: Proc SQL Insert Into syntax

Re: Proc SQL Insert Into syntax