BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
ybz12003
Rhodochrosite | Level 12

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;

1 ACCEPTED SOLUTION

Accepted Solutions
Tom
Super User Tom
Super User

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)

View solution in original post

6 REPLIES 6
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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
Rhodochrosite | Level 12

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
Super User Tom
Super User

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
Super User Tom
Super User

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
Rhodochrosite | Level 12

Awesome!  Thank you so much, Tom!

ybz12003
Rhodochrosite | Level 12

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 6 replies
  • 8848 views
  • 1 like
  • 3 in conversation