Date:         Thu, 19 Jun 1997 14:33:10 +0000
Reply-To:     /S=SGRIFFITHS/O=CLINTRIALS@MHS-CLINT.ATTMAIL.COM
Sender:       "SAS(r) Discussion" <SAS-L@UGA.CC.UGA.EDU>
From:         Steve Griffiths </S=SGRIFFITHS/O=CLINTRIALS@MHS-CLINT.ATTMAIL.COM>
Subject:      Re: Merging
Content-Type: Text/Plain; charset=us-ascii

This should also produce the desired result.
 
proc sql;
create table newcomp as
select a.*,b.*
from company a,
     sales b
where a.company=b.company
and (b.sdate > a.date1 and b.sdate<=a.date2);
quit;
 
S.G.
 
 
 
Jay Weedon wrote :
 
>On Tue, 17 Jun 1997 10:21:46 -0700, Mitch Conover
><mconover@niu.edu> wrote:
 
>>I would like to merge two data sets as these 2 examples
>illustrate: >
>>Data Company;input company date1 date2; cards
>>Ford 8806 8901
>>Ford 8907 8910
>>GM   8702 8705;
>>
>>Data Sales;input company sdate sales;cards
>>Ford 8602 3.4
>>Ford 8806 2.5
>>Ford 8807 3.1
>>Ford 8901 2.2
>>GM   8701 2.2
>>GM   8702 2.1
>>GM   8703 3.2;
>>
>>Then I want to merge the data sets and include only the observations
>>that meet the following criteria:
>>
>>if sdate gt date1 and sdate le
>date2; >
>>The resulting merged data set would look
>like: >
>>Company sdate sales date1 date2
>>Ford 8807     3.1   8806  8901
>>Ford 8901     2.2   8806  8901
>>GM   8703     3.2   8702  8705
>>
>>Thanks in advance for your help,
>>Mitch
>
>
>Not a terribly elegant solution, but I think it will work:
>* Write the company info into a single record per company;
>* I assume here the largest number of observations per company
>in set company is 20:
>Modify as needed;
>%let n=20;
>data company1;
>set company;
>by company;
>retain date1_01-date1_&n date2_01-date2_&n;
>array date1s date1_01-date1_&n;
>array date2s date2_01-date2_&n;
>if first.company then do;
>do count=1 to &n;
>date1s[count]=.; date2s[count]=.;
>end;
>count=0;
>end;
>count+1;
>date1s[count]=date1; date2s[count]=date2; if last.company;
>drop date1 date2;
>* Now match the company with the sales;
>data new; merge
>company1
>sales;
>by company;
>array date1s date1_01-date1_&n;
>array date2s date2_01-date2_&n;
>do i=1 to count;
>if date1s[i]<sdate<=date2s[i] then do;
>date1=date1s[i]; date2=date2s[i];
>end;
>end;
>if date1>. and date2>.;
>drop date1_01-date1_&n date2_01-date2_&n i count;
>run;
>
>Note that there is a potential ambiguity in your question: What happens if a
>sales observation matches two different company observations? Also, your
>crude date mechanism will collapse in the year 2000! You may want to
>consider formats or informats for the dates.
>Jay Weedon.