(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
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
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
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?
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
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.