Pull data from a text field

[ad2]

Hi,

I have a field in an database table that is a text field

Values are date ranges separated by a comma, here is an example: 9/10/06-9/12/06, 9/18/06-9/19/06, 9/26/06-9/29/06, 11/9/06-12/6/06


How do I use SAS to pull in the values and calculate the total number of days? That is the sum of the days in each date range?

Thanks,
AD2

 

[klaz2002]

First you have read the whole list of date ranges into a string (character variable). Then you need to parse (chop) the value into sets so that SAS can do calculations on this.

Here is how I would do it

data test;
  set your_data;
*** INTIALIZE THE DAY COUNTER ***;
totalnumofdays = 0;
dateranges = "9/18/06-9/19/06, 9/26/06-9/29/06, 11/9/06-12/6/06";
commas      = compress(dateranges,',','k');
numofranges = length(commas);

*** CHECK IF THERE IS AT LEAST TWO RANGES ***;
if index(commas,',') gt 0 then
   numofranges + 1;

do i = 1 to numofranges;
  rangedatetxt = scan(dateranges,i,',');
  mindatetxt   = scan(rangedatetxt,1,'-');
  mindate      = input(mindatetxt,mmddyy10.);
  maxdatetxt   = scan(rangedatetxt,2,'-');
  maxdate      = input(maxdatetxt,mmddyy10.);
  daysinrange  = maxdate - mindate;
  totalnumofdays + daysinrange;
  
end;

run;

The variable 'totalnumofdays' will have the number of overall days for all date ranges. (Please note that the COMPRESS function uses a version 9 feature and this will not work well with an earlier version of SAS.

I hope this helps you..

Klaz

 

[ad2]

Hi Klaz,

Thanks, I will try this.

Ad2