|
I cleaned up your input datasteps [you might want to look at how I wrote
them] and have one way to do this. Exists queries can be somewhat slow if
you have a lot of data, so a join may be superior in performance, but this
is a bit easier to code.
Also, disclaimer: I don't like the structure of your third dataset.
Normally you wouldn't have a dataset like this - you'd have it as four lines
with more variables (which would then work well with a join). However, as
you haven't really specified what you're using this for, I can't comment as
to the specific utility for your purposes and/or a better solution.
Data one;
informat id $5.
From_date date9.
To_date date9.
Type $1.;
input id $ from_date to_date type $;
datalines;
12345 01Jan2009 05jan2009 I
12345 06Mar2009 08Mar2009 I
45678 01jun2009 10jun2009 I
99899 28Jan2009 03feb2009 I
99811 05May2009 29May2009 I
44564 01apr2009 02apr2009 I
12312 04dec2009 19dec2009 I
;;;;
Run;
Data two;
informat id $5. from_date date9. Type $1.;
input id $ from_date type $;
datalines;
12345 04Jan2009 P
12345 06jun2009 P
12345 21dec2009 P
45678 10jun2009 P
45678 29may2009 P
99899 15jan2009 P
12312 06dec2009 P
12312 03dec2009 P
99811 01may2009 P
99811 29may2009 P
99811 03dec2009 P
44564 01mar2009 P
44564 15apr2009 P
;;;;
Run;
proc sql;
create table three as
select * from one where exists (
select 1 from two where one.id=two.id and (two.from_date ge
one.from_date and two.from_date le one.to_date)
)
union all
select id, from_date, . as to_date, type from two where exists(
select 1 from one where one.id=two.id and (two.from_date ge
one.from_date and two.from_date le one.to_date)
)
;
quit;
-Joe
On Tue, Jan 19, 2010 at 11:32 AM, V V B <vibooks@comcast.net> wrote:
> Hi All,
>
> I'm trying to subset or keep data based on a date range. The data in the
> table titled data one (below) includes the date range. The data in the
> table titled two (below) represents the information I need to keep based
> on the date range in table one.
>
> If the date in table 'Data two' falls within the date range in table 'Data
> one' I need to keep both lines.
>
> I've listed examples of the data and the desired output.
>
> Your help will be most appreciated as I just don't know how to code for
> this situation.
>
> I'm using PC SAS 9.1.3.
>
> Thanks! Vivian
>
> Data one:
> input id $5. From_date date9. To_date date9. Type
> $1.;
>
> datalines;
> 12345 01Jan2009 05jan2009 I;
> 12345 06Mar2009 08Mar2009 I;
> 45678 01jun2009 10jun2009 I;
> 99899 28Jan2009 03feb2009 I;
> 99811 05May2009 29May2009 I;
> 44564 01apr2009 02apr2009 I;
> 12312 04dec2009 19dec2009 I;
>
> Run;
>
> Data two;
> Input id $5. from_date date9. Type $1.;
>
> datalines;
>
> 12345 04Jan2009 P;
> 12345 06jun2009 P;
> 12345 21dec2009 P;
> 45678 10jun2009 P;
> 45678 29may2009 P;
> 99899 15jan2009 P;
> 12312 06dec2009 P;
> 12312 03dec2009 P;
> 99811 01may2009 P;
> 99811 29may2009 P;
> 99811 03dec2009 P;
> 44564 01mar2009 P;
> 44564 15apr2009 P;
> Run;
>
> Desired output:
>
> 12345 01Jan2009 05jan2009 I;
> 12345 04Jan2009 P;
> 45678 01jun2009 10jun2009 I;
> 45678 10jun2009 P;
> 99811 05May2009 29May2009 I;
> 99811 29may2009 P;
> 12312 04dec2009 19dec2009 I
> 12312 06dec2009 P;
>
| 
