CODEWITHSUNDEEP
sqlsas
qfd
qfd

Reputation: 788

extracting from dataset using another dataset in SAS

I have the following dataset FULLDS

 id  date         value
 A  "05/31/2000"   10
 B  "06/31/2000"    11

I have another dataset NewDS which is 

id  startdate enddate
A   "04/30/2000"  "05/31/2002"
etc

I would like to only keep data in the FULLDS that is matched according to its in NewDS and is only between the start and end dates

Any help is greatly appreciated. Thank you!

Upvotes: 0

Views: 86

Answers (2)

Joe
Joe

Reputation: 63424

A somewhat faster way to do this (as well as a SASsy way) is to use formats.

Take NewDS, make a new dataset based on that called fmts or some such, and rename the variables to start and end, create a variable label with a value of 'VALID', and rename ID to fmtname.

Then proc format cntlin=fmts; quit; (fmts = name of your dataset made above).

Then you use putn like so:

data want;
set fullds;
if putn(date,id) = 'VALID';
run;

This assumes all date variables (in both datasets) are truly date values, and not character variables. If they are, then start, end, and the value going into the putn will all need to be input to dates.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269773

You can do this using proc sql. The following pulls the data using a select:

proc sql;
    select f.*
    from FULLDS f join
         NewDS n
         on f.id = n.id and f.date between n.startdate and n.enddate;

You can actually delete the non-matching rows by doing:

proc sql;
    delete from f
        where not exists (select 1
                          from NewDs n
                          where f.id = n.id and f.date between n.startdate and n.enddate
                         ) ;

Upvotes: 2

Related Questions