BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
lior
Calcite | Level 5

(this is a correction to the question I sent yesterday:)

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

1 ACCEPTED SOLUTION

Accepted Solutions
Ksharp
Super User

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

4 REPLIES 4
Ksharp
Super User

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
Calcite | Level 5

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
Super User

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
Barite | Level 11

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;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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