LISTSERV at the University of Georgia
Menubar Imagemap
Home Browse Manage Request Manuals Register
Previous (more recent) messageNext (less recent) messagePrevious (more recent) in topicNext (less recent) in topicPrevious (more recent) by same authorNext (less recent) by same authorPrevious page (June 1997, week 3)Back to main SAS-L pageJoin or leave SAS-L (or change settings)ReplyPost a new messageSearchProportional fontNon-proportional font
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.


Back to: Top of message | Previous page | Main SAS-L page