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

Hi Forum,

I'm using SAS Enterprise Guide 4.3 with Windows 7 in case it's useful.

I have 2 tables, with a Month_Id written as '201310' to denote October 2013 for example.

Problem is, I have one table with this Column created as an Integer & another as a Varchar.

What is the best way to change one of them to the others datatype so I can join them?

Thanks

1 ACCEPTED SOLUTION

Accepted Solutions
Patrick
Opal | Level 21

The EG Query Builder is a wizard which generates SQL code. As with all wizards there are always limitations.

What proposes would work but I haven't found a way using the wizard to generate such code.

If you need an inner join (so the result only containing matching rows) then you could "trick" the wizard by not defining a join at all but by defining a filter (this results in an implicit inner join).

I'm using an expression 'input(character_key, best16.)' which converts the digits in the character variable into a numeric value before comparing with the numeric key.

Capture.PNG

If you need another sort of join (eg. a left join) then I believe you need first a query builder node to create a calculated field which contains this numeric value (so you're using the input() there) and then you use the output table of this query in the next query builder where you join the 2 tables together.

View solution in original post

4 REPLIES 4
ballardw
Super User

Join on put(integervaluedvariable, f6.) = characterversion

pcfriendly
Calcite | Level 5

Hi ballardw,

Thanks for the response but I don't know what you mean?

Could you explain it in terms of using a Query builder with 2 tables, one table 'Table1' with the field 'Mon_Yr' as an Integer datatype & another table 'Table2' with the field 'Mon_Yr' as a Varchar datatype?

Thanks

Patrick
Opal | Level 21

The EG Query Builder is a wizard which generates SQL code. As with all wizards there are always limitations.

What proposes would work but I haven't found a way using the wizard to generate such code.

If you need an inner join (so the result only containing matching rows) then you could "trick" the wizard by not defining a join at all but by defining a filter (this results in an implicit inner join).

I'm using an expression 'input(character_key, best16.)' which converts the digits in the character variable into a numeric value before comparing with the numeric key.

Capture.PNG

If you need another sort of join (eg. a left join) then I believe you need first a query builder node to create a calculated field which contains this numeric value (so you're using the input() there) and then you use the output table of this query in the next query builder where you join the 2 tables together.

pcfriendly
Calcite | Level 5

Thanks ballardw/Patrick,

I'll give these a try.  :smileycool:


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!

SAS Enterprise Guide vs. SAS Studio

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.

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
  • 4 replies
  • 7662 views
  • 3 likes
  • 3 in conversation