Case Sensitive for Left Join ?

[ybz12003]

Hello:

I have a question.   I use proc sql left join to compare the two tables below.  However, I found the uppercase in Name of &state.outname is not recognized by Var of Dct.  For example, the string 'CODE_CO' in Name of table '&state.name' are actually the same as 'Code_co' in Var of Table 'Dct'.   Please advide how to fix this probelm.  Thank you!

proc sql;

CREATE TABLE dismatch AS

SELECT &state.outname.*, Dct.*

FROM &state.outname AS a

left JOIN work.Dct AS b

ON a.Name = b.Var;

quit;

[RW9]

Actually to add, re-reading your question, it may be that you want it as is.  Its quite hard to tell from what you post.  Some example test data in the form of a datastep, posted into a code window ({i} above post) and what you want out would clearly show your problem.

I am sure we have been over this before.  Macro variables, how to use them:

&<macro variable name>.

^                                      ^

First character starts the macro variable, the dot at the end finishes the macro variable.  Use this syntax all the time to avoid problems.

These two lines:

SELECT &state.outname.*, Dct.*

FROM &state.outname AS a

If STATE=ABC resolve to:

SELECT ABCoutname.*, Dct.*

FROM ABCoutname AS a

So you need a dot to finish the macro variable and a dot to separate the two parts of the code:

SELECT &state..outname.*, Dct.*

FROM &state..outname AS a

[ybz12003]

I use double dots.  but got an error message.  My orignal code works fine.

961 proc sql;

962 CREATE TABLE dismatch AS

22: LINE and COLUMN cannot be determined.

NOTE 242-205: NOSPOOL is on. Rerunning with OPTION SPOOL might allow recovery of the LINE and COLUMN

where the error has occurred.

ERROR 22-322: Syntax error, expecting one of the following: a quoted string, !, !!, &, *, **, +, ',',

-, /, <, <=, <>, =, >, >=, ?, AND, AS, BETWEEN, CONTAINS, EQ, EQT, FORMAT, FROM, GE,

GET, GT, GTT, IN, INFORMAT, INTO, IS, LABEL, LE, LEN, LENGTH, LET, LIKE, LT, LTT, NE,

NET, NOT, NOTIN, OR, TRANSCODE, ^, ^=, |, ||, ~, ~=.

963 SELECT &state..outname7.*, IFDCTDct.*

200: LINE and COLUMN cannot be determined.

NOTE: NOSPOOL is on. Rerunning with OPTION SPOOL might allow recovery of the LINE and COLUMN where

the error has occurred.

ERROR 200-322: The symbol is not recognized and will be ignored.

964 FROM &state..outname7 AS a

965 left JOIN ZKMON.IFDCTDct AS b

966 ON a.Name = b.IFDCTvar;

967 quit;

[Tom]

Your list of variables is confused in the SELECT statement.  Use the ALIAS you defined in the FROM clause.

If you are using the macro variable STATE as a prefix on the member name of the first input table then you should use.

proc sql;
create table dismatch as
  select a.*, dct.*
  from &state.outname as a
  left join work.dct as b
    on upcase(a.name) = upcase(b.var)
;
quit;

So if the macro variable STATE is set to NY are you looking for a dataset named NYOUTNAME or NY.OUTNAME?

If the macro variable STATE has the libref and OUTNAME is the complete member name then you need to add another period since the currrent one is being used to mark the end of the macro variable's name.

[Tom · Accepted solution]

If your data has mixed case and you want to ignore that in the join criteria then use the UPCASE() (or lowcase()) function.

FROM &state.outname AS a
left JOIN work.Dct AS b
ON upcase(a.Name) = upcase(b.Var)

[ybz12003]

Awesome!  Thank you so much, Tom!

[ybz12003]

Oh, I got it now.  Thank you for your suggestion, Tom ans RW9.