why sas sql procedure instead of merge procedure when combine the data?

[lucky66]

Policy data:
Policy# age Policy take effective date
1 24 01/01/2008
1 24 01/07/2008
2 50 06/15/2007
3 25 04/11/2006...

Claim data:
Policy# loss Claim date
1 1000 02/05/2008
2 2000 06/30/2007
3 3000 07/15/2007
4 4000 ...

given the above two data sets and we are asked to combine these two data sets to find the age for each claim loss.(This requires that the claim date is within 180 days since the policy take effective, in other words that the claim date- date the policy take effective should be smaller or equal to 180 days.) The question is why can't use the merge procedure using the following code? Instead I should use SAS SQL procedure? If someone knows, could you explain why and how to combine the data with PROC SQL? Thank you so much.
Data b; merge policy data claim data; by policy#; where claim date- policy take effective date<=180;run;

[Patrick]

Hi

Having both SQL joins and the SAS data step as options to combine data sets just gives you more options.

If the data source is in a data base like Oracle then using a SQL join makes it easier to code something which will be sent and processed on the DB server (instead of first loading all data into SAS and then process it).

Also clients like EG and DI Studio often create SQL code. Understanding the generated code is important when you have to debug it.

So learning SQL is a very good idea IMHO.

Below a SQL fragment as a starter:

proc sql;
select c.*, p.effective_date
from claim as c ,policy as p
where p.policyid = c.policyid and ....
;
quit;

You will still have to work on the "where clause" to link the correct rows together.
You find a lot of guidance and examples in the Online SAS Doc.

HTH
Patrick

[art297]

You might want to read:
http://www2.sas.com/proceedings/sugi22/ADVTUTOR/PAPER39.PDF

When you have one to many or many to many merges that you want to accomplish, proc sql is the ONLY way to do it.

Art

[lucky66]

I see, thank you all.

[Ksharp]

No .Art T
I do not think so.
Data step also can do Cartestian Join , just need more code than sql.
I am just coding in data step to simulate proc sql 's Cartestian Product.
And at the opposite, There are some case that only data step can do, sql cann't.
And for the sake of large dataset (such as 100G),
Proc sql is almost not to run,now it is show time for SAS data step.


Ksharp Message was edited by: Ksharp