DATA Step, Macro, Functions and more

Applying join left procedure with two Criteria

Accepted Solution Solved
Reply
Contributor
Posts: 38
Accepted Solution

Applying join left procedure with two Criteria

(this is a correction to the question I sent yesterdaySmiley Happy

my database look like that:

file1

Company_numberdate
11111131/03/2012
11111130/06/2012
22222231/03/2012
22222230/06/2012

file2

  Company_number     date company_price
.
.
111111.28/03/201210
11111129/03/201212
11111130/03/201211
11111131/03/201212
11111101/04/201214
11111127/06/201216
11111128/06/201217
11111129/06/201218
11111130/06/201219
11111101/07/201220
11111102/07/201221
222222.28/03/201230
22222229/03/201233
22222230/03/201235
22222231/03/201233
22222201/04/201232
22222227/06/201234
22222228/06/201235
22222229/06/201236
22222230/06/201237
22222201/07/201238
22222202/07/201239

the combined_file should look like that:

Company_numberdatecompany_price
11111131/03/201212
11111130/06/201219
22222231/03/201233
22222230/06/201237

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: 9,676

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

View solution in original post


All Replies
Solution
‎11-26-2014 09:11 AM
Super User
Posts: 9,676

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: 9,676

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: 336

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.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 263 views
  • 3 likes
  • 3 in conversation