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:         Tue, 17 Jun 1997 15:19:09 GMT
Reply-To:     Jay Weedon <j_weedon@ESCAPE.COM>
Sender:       "SAS(r) Discussion" <SAS-L@UGA.CC.UGA.EDU>
From:         Jay Weedon <j_weedon@ESCAPE.COM>
Organization: None
Subject:      Re: Merging
Content-Type: text/plain; charset=us-ascii

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