Solved
Contributor
Posts: 38

# Applying join left procedure with two Criteria

(this is a correction to the question I sent yesterday

my database look like that:

file1

 Company_number date 111111 31/03/2012 111111 30/06/2012 222222 31/03/2012 222222 30/06/2012

file2

 Company_number date company_price . . 111111 .28/03/2012 10 111111 29/03/2012 12 111111 30/03/2012 11 111111 31/03/2012 12 111111 01/04/2012 14 111111 27/06/2012 16 111111 28/06/2012 17 111111 29/06/2012 18 111111 30/06/2012 19 111111 01/07/2012 20 111111 02/07/2012 21 222222 .28/03/2012 30 222222 29/03/2012 33 222222 30/03/2012 35 222222 31/03/2012 33 222222 01/04/2012 32 222222 27/06/2012 34 222222 28/06/2012 35 222222 29/06/2012 36 222222 30/06/2012 37 222222 01/07/2012 38 222222 02/07/2012 39

the combined_file should look like that:

 Company_number date company_price 111111 31/03/2012 12 111111 30/06/2012 19 222222 31/03/2012 33 222222 30/06/2012 37

meaning I need the joint left to apply two Criteria:

date of file1 = date of file2

Company_number of file1 = Company_number of file2.

I tried registere it like this

proc sql;

create table file_combined as

select a.*,b.company_price

from file1 a

left join file2 b

on put(a. date, date.) eq put   (b.date, date.)

and (a.company_number, \$6.) eq put (b. company number, \$6.)

order by company_number, date;

quit;

run;

but with no succeed. any ideas how can I put two Criteria  in left_join procedure?

thanks,

Lior

Accepted Solutions
Solution
‎11-26-2014 09:11 AM
Super User
Posts: 10,784

## Re: Applying join left procedure with two Criteria

You should use Cartesian Product ,not left join.

```data file1;
infile cards expandtabs truncover;
input Company_number \$ date : ddmmyy12.;
format date ddmmyy10.;
cards;
111111     31/03/2012
111111     30/06/2012
222222     31/03/2012
222222     30/06/2012
;
run;

data file2 ;
infile cards expandtabs truncover;
input Company_number \$  date : ddmmyy12. company_price ;
format date ddmmyy10.;
cards;
111111     28/03/2012     10
111111     29/03/2012     12
111111     30/03/2012     11
111111     31/03/2012     12
111111     01/04/2012     14
111111     27/06/2012     16
111111     28/06/2012     17
111111     29/06/2012     18
111111     30/06/2012     19
111111     01/07/2012     20
111111     02/07/2012     21
222222     28/03/2012     30
222222     29/03/2012     33
222222     30/03/2012     35
222222     31/03/2012     33
222222     01/04/2012     32
222222     27/06/2012     34
222222     28/06/2012     35
222222     29/06/2012     36
222222     30/06/2012     37
222222     01/07/2012     38
222222     02/07/2012     39
;
run;
proc sql;
create table file_combined as
select a.*,b.company_price
from file1 a,file2 b
where a.date = b.date and a.company_number = b.company_number
order by company_number,date;
quit;
```

Xia Keshan

All Replies
Solution
‎11-26-2014 09:11 AM
Super User
Posts: 10,784

## Re: Applying join left procedure with two Criteria

You should use Cartesian Product ,not left join.

```data file1;
infile cards expandtabs truncover;
input Company_number \$ date : ddmmyy12.;
format date ddmmyy10.;
cards;
111111     31/03/2012
111111     30/06/2012
222222     31/03/2012
222222     30/06/2012
;
run;

data file2 ;
infile cards expandtabs truncover;
input Company_number \$  date : ddmmyy12. company_price ;
format date ddmmyy10.;
cards;
111111     28/03/2012     10
111111     29/03/2012     12
111111     30/03/2012     11
111111     31/03/2012     12
111111     01/04/2012     14
111111     27/06/2012     16
111111     28/06/2012     17
111111     29/06/2012     18
111111     30/06/2012     19
111111     01/07/2012     20
111111     02/07/2012     21
222222     28/03/2012     30
222222     29/03/2012     33
222222     30/03/2012     35
222222     31/03/2012     33
222222     01/04/2012     32
222222     27/06/2012     34
222222     28/06/2012     35
222222     29/06/2012     36
222222     30/06/2012     37
222222     01/07/2012     38
222222     02/07/2012     39
;
run;
proc sql;
create table file_combined as
select a.*,b.company_price
from file1 a,file2 b
where a.date = b.date and a.company_number = b.company_number
order by company_number,date;
quit;
```

Xia Keshan

Contributor
Posts: 38

## Re: Applying join left procedure with two Criteria

thank you, its work.

one more question: if the file1 there is date that don't appear at file2, is there a way to bring the company_price of  the previous day (from the upper row)?

for example if there is no compnany price for 31/03/2012 but there is company_price for 30/03/2012, is there any statement that will bring the compamy_price of 30/03/2012 and will present it as the company_price of 30/03/2012?

Super User
Posts: 10,784

## Re: Applying join left procedure with two Criteria

Sure. For this scenario , I would like to use data step, especially when you have a big table.

```data file1;
infile cards expandtabs truncover;
input Company_number \$ date : ddmmyy12.;
format date ddmmyy10.;
cards;
111111     31/03/2012
111111     30/06/2012
222222     31/03/2012
222222     30/06/2012
;
run;

data file2 ;
infile cards expandtabs truncover;
input Company_number \$  date : ddmmyy12. company_price ;
format date ddmmyy10.;
cards;
111111     28/03/2012     10
111111     29/03/2012     12
111111     30/03/2012     11
111111     01/04/2012     14
111111     27/06/2012     16
111111     28/06/2012     17
111111     29/06/2012     18
111111     30/06/2012     19
111111     01/07/2012     20
111111     02/07/2012     21
222222     28/03/2012     30
222222     29/03/2012     33
222222     30/03/2012     35
222222     31/03/2012     33
222222     01/04/2012     32
222222     27/06/2012     34
222222     28/06/2012     35
222222     29/06/2012     36
222222     30/06/2012     37
222222     01/07/2012     38
222222     02/07/2012     39
;
run;
data want;
set file2 file1(in=inb);
by Company_number  date ;
retain price .;
if Company_number ne lag(Company_number) then call missing(price);
if not missing(company_price) then price=company_price;
if inb;
drop  company_price;
run;

```

Xia Keshan

Super Contributor
Posts: 355

## Re: Applying join left procedure with two Criteria

ksharp is probably right, but I think this might work as well (I am not quite sure why you're changing the variable type though):

Proc SQL;

Create Table file_combines As

Select A.*,

B.Company_Price

From

(Select Date Format=DDMMYY10., Put(Company_number,6.) As C_Nr Format=\$6. Length=6 From File1) As A

Left Join

(Select Date Format=DDMMYY10., Put(Company_number,6.) As C_Nr Format=\$6. Length=6, Company_price From File2) As B

On A.Date eq B.Date AND

A.C_Nr eq B.C_Nr

Order By C_Nr, Date;

Quit;

🔒 This topic is solved and locked.