Applying join left procedure with two Criteria

[lior]

(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

[Ksharp · Accepted solution]

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

[lior]

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?

[Ksharp]

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

[user24feb]

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;