Use IF.....THEN with dates in multiple SAS datasets

[vassilisef]

Hey !

i am not a much experienced user and i would like your help.
I have two SAS datasets only including dates.
Dataset1: 'Pract.Listofdates' containing just one variable
ie. a list of Xdates
Dataset2: 'Pract.Nber_expansion' containing two variables/ lists of dates : a startdate & an enddate so that each observation defines one time interval.

My aim is to see whether each individual Xdate (1st dataset) can be traced in ANY of the intervals defined by the 2nd dataset.

Namely, somehow, i need to retain one Xdate value until it runs through all the date intervals before i move to the next Xdate.

My best effort (that is wrong .. although i did not have any Errors at the log) would be:

data pract.assignstate;
set Pract.Listofdates Pract.Nber_expansion;
retain retainedate;
do retainedate=Xdates;
	length status $11;
	if startdate le retainedate le enddate then status='expansion';
	else status='contraction';
end;
keep Xdates status ;
run;

Any corrections or alternative coding (with SQL etc) are strongly welcomed.

Thanks in advance.

 

[ChrisW75]

Yeah, you can join the 2 tables/datasets together using proc sql.

proc sql;
  create table test as
  select A.Xdate
  from pract.listofdates  A
     left join
       pract.Nber_expansion  B
    where A.Xdate between B.startdate and B.enddate
  ;
quit;

... should do the trick.
If between doesn't work (can't remember if that construction, which works in Oracle, is accepted by SAS, change that line to

  where A.Xdate >= B.startdate
    and A.Xdate <= B.enddate

Chris
Business Analyst, Code Monkey, Data Wrangler.
SAS Guru.

 

[vassilisef]

Chris ,

thank you very much for helping out.
In fact, this way i was introduced to SQL .
Just for the record, the code i used, using your advice is:

proc sql;
create table pract.assignstate as
select  ex.exdate,
		interval.state
	from pract.exdatesample as EX left join 
		 pract.Nber as interval
on interval.startdate le ex.exdate le interval.enddate;
quit;

Keep up the excellent support to all of us beginners

 

[ChrisW75]

No worries. Make sure you keep an eye on the number of records coming out. I'd recommend putting a unique key on the two tables before the join, keep both unique keys in the output file, then check for duplication, just in case. This method could result in a large number of duplicate records.

Chris
Business Analyst, Code Monkey, Data Wrangler.
SAS Guru.