0

I have two tables with multiple columns. Now I want to merge them using like operator. My code is like following

proc sql;

    select a.*,b.*
    from tera.SFTP as a,home.SFTP_1 as b
    where a.zip_c like ('%b.zip_extract%')

;
quit;

I am getting Page Break messages and no result is returned. The column types for a.zip_c is Char and length is 50 while for b.zip_extract its Char with length 6.

LonelySoul
  • 1,212
  • 5
  • 18
  • 45
  • You're going to want to provide more detail than "it's not working". is there an error? are you not getting a result set? – Codeman Jul 29 '14 at 19:30
  • Thats the problem. There is no error. Just "Page Break" as output. – LonelySoul Jul 29 '14 at 19:31
  • You should make that clear in the question. Instead of `its not working`, write `I get no records returned from the query`. – Joe Jul 29 '14 at 19:36
  • 1
    You should also provide more information - such as example datasets including the lengths/types of variables, such that we can show you a query that works on the example data. – Joe Jul 29 '14 at 19:43

2 Answers2

2

The problem is that you are matching on the string, b.zip_extract, not the column.

Try:

select a.*,b.*
from tera.SFTP as a,home.SFTP_1 as b
where a.zip_c like '%' || b.zip_extract || '%'
Vulcronos
  • 3,428
  • 3
  • 16
  • 24
  • SAS won't allow `+`; you need `||`, or the `cats` family of functions, to concatenate [and if `b.zip_Extract` is numeric, the latter specifically should be used]. – Joe Jul 29 '14 at 19:35
  • Thats a nice lead. However I am getting error like following. ERROR: Expression using addition (+) requires numeric types. ERROR: Expression using addition (+) requires numeric types. ERROR: LIKE operator requires character operands.. And both the columns are basically CHAR – LonelySoul Jul 29 '14 at 19:36
  • @LonelySoul My bad. I am too used to using + from other languages. I updated my code to use ||. Joe suggests cats if the field is a number. – Vulcronos Jul 29 '14 at 19:38
  • Base SAS doesn't overload the `+` operator; it simply casts the strings as numeric and adds them. However, `PROC SQL` doesn't permit that automatic casting for some reason. – Joe Jul 29 '14 at 19:42
2

Merging on like is not a great idea; it doesn't use indices and doesn't use a lot of the optimizations you otherwise get. However, sometimes it's necessary.

In SAS, though, I'd do this differently [and in most other SQLs...]

proc sql;

    select a.*,b.*
    from tera.SFTP as a,home.SFTP_1 as b
    where find(a.zip_c,b.zip_extract)
;

quit;

That accomplishes the same thing as LIKE, but is more likely to allow SAS to use indices and optimizations, and is more clearly written.

To deal with possible column length issues, use TRIM:

data have_a;
 length zip_c $50;
 input @1 zip_c $50.;
 datalines;
12345 99999
67890 99999
88001 99999
37013 99999
60037 99999
;;;;
run;

data have_b;
 length zip_extract $7;
 input zip_extract $;
 datalines;
12345
37013
99998
;;;;
run;

proc sql;
  title "No Rows!";
    select a.*,b.*
    from have_a as a,have_b as b
    where find(a.zip_c,b.zip_extract)
;

quit;
proc sql;
  title "Rows!";
    select a.*,b.*
    from have_a as a,have_b as b
    where find(a.zip_c,trim(b.zip_extract))
;

quit;
Joe
  • 62,789
  • 6
  • 49
  • 67
  • It's running for some time and then Page Break. So something did happened. – LonelySoul Jul 29 '14 at 19:39
  • You probably need to use something additional, depending on your data. You didn't provide us much to go on. For example, if `b.zip_extract` is not always fully filling the field (so it's a 10 character field, that `b.zip_Extract` usually only takes up 5 spots in), it may need to be `trim()`ed. – Joe Jul 29 '14 at 19:40
  • My Bad. a.Zip_c is Character with length 50 amd b.zip_extract is Character with length 7. I tried the trim() but of no use. – LonelySoul Jul 29 '14 at 19:44
  • Added an example using trim. If that doesn't work, then I don't have a good answer for you without more information - no reason this shouldn't work if your data is properly formed. – Joe Jul 29 '14 at 19:48
  • Joe, I just updated my question with more details. I have started believing that it has to do something with the csv files from where I was getting the data. – LonelySoul Jul 29 '14 at 19:57
  • Joe, will it be possible if you can explain about how the "find" works in the joins. This is the first time , I saw this. – LonelySoul Jul 29 '14 at 21:54
  • 1
    `find` is just a regular function there as in any other place. It returns a value, namely the position it finds argument 2 in argument 1, or 0 if not found (0=false). – Joe Jul 30 '14 at 03:03