Providing software solutions since 1976
Sample 24756: Combining observations when there is no common variable
 |  |  |  |  |  |
Combine observations where COMPDATE from BILLS falls between STDATE and ENDDATE in PROJECTS when there is no common variable in the two data sets.
Note:
This is Example 2.3 from "Combining and Modifying SAS Data Sets - Examples".
These sample files and code examples are provided by SAS Institute Inc. "as is" without warranty of any kind, either express or implied, including but not limited to the implied warranties of merchantability and fitness for a particular purpose. Recipients acknowledge and agree that SAS Institute shall not be liable for any damages whatsoever arising out of their use of this material. In addition, SAS Institute will provide no support for the materials contained herein.
/****************************************************************************/
/* Use the looping action of the DATA step to access an observation from */
/* one data set on each iteration while reading all observations from a */
/* second data set to look for a match. To read the second data set, */
/* use the SET statement with POINT= and NOBS= options in a DO loop to */
/* access all observations sequentially by observation number until a */
/* match is found. Then you can test a condition for each one to determine */
/* whether combining the information from the current observation of */
/* each data set is appropriate and write an observation to a new data set */
/* when the condition is met. Optionally, you can write a note to the SAS */
/* log when no match for a project is found. */
/* */
/* You can perform the same task with PROC SQL, with the exception */
/* of writing a note to the log under a certain condition. See */
/* "Related Technique". */
/****************************************************************************/
options nodate nonumber ls=80;
data projects;
input project $ stdate : mmddyy8. enddate : mmddyy8.;
format stdate enddate mmddyy8.;
datalines;
BASEMENT 01/09/95 01/27/95
FRAME 02/01/95 02/12/95
ROOFING 02/15/95 02/20/95
PLUMB 02/22/95 02/28/95
WIRE 03/02/95 03/05/95
BRICK 03/07/95 03/29/95
;
data bills;
input workid compdate : mmddyy8. charge : 7.2;
format compdate mmddyy8. charge dollar10.2;
datalines;
1234 01/17/95 944.80
2225 02/18/95 1280.94
3879 03/04/95 888.90
8888 03/21/95 2280.87
;
data combine1(drop=found);
set projects;
/* Set FOUND back to zero. FOUND will be used to stop the */
/* DO UNTIL loop after a match has been found. */
found=0;
/* Read an observation from BILLS until a match is found */
/* or until all observations have been read. */
do i=1 to n until (found);
set bills point=i nobs=n;
/* When the condition is met, set FOUND to 1 and write */
/* an observation to COMBINE1. */
if stdate <= compdate <= enddate then
do;
found=1;
output;
end;
end;
/* If no observations match, write a note to the log */
if not found then put 'No bills exist for: ' project
'with start date ' stdate 'and enddate ' enddate +(-1) '.';
run;
title 'COMBINE1';
proc print data=combine1;
run;
/* Related technique using PROC SQL -- PROC SQL joins the PROJECTS */
/* and BILLS tables to produce a new table COMBINE2. Conceptually, */
/* the join results in an internal table that matches every row in */
/* PROJECTS with every row in BILLS. Using that internal table, */
/* the WHERE clause determines that only the rows that have a value */
/* of COMPDATE that is between STDATE and ENDDATE will be in the */
/* resulting table. */
proc sql;
create table combine2 as
select *
from projects, bills
where compdate between stdate and enddate;
quit;
These sample files and code examples are provided by SAS Institute Inc. "as is" without warranty of any kind, either express or implied, including but not limited to the implied warranties of merchantability and fitness for a particular purpose. Recipients acknowledge and agree that SAS Institute shall not be liable for any damages whatsoever arising out of their use of this material. In addition, SAS Institute will provide no support for the materials contained herein.
COMBINE1 Obs project stdate enddate workid compdate charge 1 BASEMENT 01/09/95 01/27/95 1234 01/17/95 $944.80 2 ROOFING 02/15/95 02/20/95 2225 02/18/95 $1,280.94 3 WIRE 03/02/95 03/05/95 3879 03/04/95 $888.90 4 BRICK 03/07/95 03/29/95 8888 03/21/95 $2,280.87
Combine observations based on some criteria, even when there is no common variable in the two data sets.
| Type: | Sample |
| Topic: | SAS Reference ==> Procedures ==> SQL SAS Reference ==> DATA Step SAS Reference ==> Statements ==> File-handling ==> SET ==> with POINT= SAS Reference ==> Statements ==> File-handling ==> SET Data Management ==> Manipulation and Transformation ==> Combining and Modifying Data Sets |
| Date Modified: | 2005-12-08 11:34:30 |
| Date Created: | 2004-09-30 14:09:11 |
Operating System and Release Information
| Product Family | Product | Host | SAS Release | |
| Starting | Ending | |||
| SAS System | Base SAS | All | n/a | n/a |
