While joining data on server i want to use compress function on where condition

Reply
Occasional Contributor
Posts: 10

While joining data on server i want to use compress function on where condition

[ Edited ]

Hi All,

 

I want to join  tables on server and its have huge data and not proper format.

Please see the attached file for reference, In which two sheets are there.

a: my client provided me a file in which i have Phone_number and its status (Status means wheathernumber is Active or not) 

Like Sheet Name: Info_1

 

b: 2nd table is on server have Phone_number but not in proper format and New_mbr_id for every individual phon_nbr, This table is in server. 

for this please see sheet Name: Info_2.

 

Please help me out. Its urgent.

I am tring below code while joing this data.

 

for example i am matching info_1 on client server with teradata table info_2 


proc sql;
create table test as

select a.phone_nbr, b.New_mbr_id, a.Status from

phon_1 a , phon_2 b
where a.phone_nbr = (Input(compress(b.Phone_nbr,'-')))
;
quit;

Super User
Posts: 10,516

Re: While joining data on server i want to use compress function on where condition

I strongly recommend before attempting to join things that the phone number be put into "proper format".

 

And instead of a cartesian join (combines all records in one table with all in the other) and then using where to use a JOIN ON the phone numbers.

 

Have you tested that "(Input(compress(b.Phone_nbr,'-')" results in something that will match?

 

I suspect that part of your issue revolves around how the two files were imported into SAS. It might be worth the effort to save the XLXS as CSV and then (assuming you used proc import) to re-import. NOTE the log results will contain the datastep code used to read the files. You can likely modify the reads so that both of the files will have the phone numbers in the "proper format".

 

 

Occasional Contributor
Posts: 10

Re: While joining data on server i want to use compress function on where condition

Thanks for the reply.

It will not give any cartisian result.

both table have unique phone_number. only concern is info_2 table is on server and unproper format.

 

I have only concern that 2nd table means Info_2 phone_number is not in correct format.

is there any to solve it, then please let me know.

by that code i am getting error.

 

 

Super User
Posts: 17,865

Re: While joining data on server i want to use compress function on where condition

Because you haven't explicitly specified a join type it will be a Cartesian join as that's the default and then the results are filtered on the where condition. 

 

Explicitly specify your join and use an on instead of where. 

Super User
Posts: 10,516

Re: While joining data on server i want to use compress function on where condition


ManoharNathGupta wrote:

Thanks for the reply.

 

I have only concern that 2nd table means Info_2 phone_number is not in correct format.

is there any to solve it, then please let me know.

by that code i am getting error.

 

 


Show some examples of the data from both files and the log error messages.

Many of us are not going to look at XLSX files or they are blocked from any but specified by our IT settings.

Occasional Contributor
Posts: 10

Re: While joining data on server i want to use compress function on where condition

Here is

info_1 data

 

Phone_nbr Status
123456789 Active
789456123 Active
987654321 InActive
741852963 Active
147258369 InActive
987654321 Active
321654987 InActive
963852741 Active
123456789 InActive

 

info_2 data

 

Phone_nbr New_mbr_id
123456789++ 111
789456123-- 222
987-654-321 333
741852963aaaa 444
147258369bbb 555
987654321**** 666
321654987tttt 777
???963852741 888
123-456-789 999

 

Below is the code for reference.

 

proc import datafile = "C:\Users\Manohar\Desktop\Export\Phone_number.xls"
out = phon_1 /* do for phon_2*/
dbms = excel replace;
sheet = "Info_1"; /*Same code for sheet info_2*/
getnames = Yes;
run;

 

proc sql;
create table test as
select a.phone_nbr, b.New_mbr_id, a.Status from
phon_1 a , phon_2 b
where a.phone_nbr = (Input(compress(b.Phone_nbr,'-')))
;
quit;

 

 

and here is log error

 

ERROR 22-322: Syntax error, expecting one of the following: !, !!, &, *, **, +, ',', -, /, <, <=, <>,
=, >, >=, ?, AND, BETWEEN, CONTAINS, EQ, EQT, GE, GET, GT, GTT, IN, IS, LE, LET, LIKE,
LT, LTT, NE, NET, NOT, NOTIN, OR, ^, ^=, |, ||, ~, ~=.

 

ERROR 76-322: Syntax error, statement will be ignored.

 

Super User
Posts: 17,865

Re: While joining data on server i want to use compress function on where condition

You need to post more of the log so we can see where in your code the error is being generated.

 

Super User
Super User
Posts: 6,502

Re: While joining data on server i want to use compress function on where condition

You need to supply an INFORMAT if you want to use the INPUT() function.  If you want to new variable to be numeric use a numeric informat.

Input(compress(b.Phone_nbr,'-'),20.)
Ask a Question
Discussion stats
  • 7 replies
  • 738 views
  • 0 likes
  • 4 in conversation