How to deal with Date Ranges with Missing Values from Excel

[JEFFREY CANTER]

All,

I have a project where I'm trying to determine if a date falls within one of three ranges.  Part of the problem is that the data is coming in from an Excel spreadsheet with some missing values.  The blank cells have formulas in the cells which is causing me to use the missing values code below.  The actual data has four fields per effective range so I trimmed the sample data down to make if easier to look at.  At the bottom  is an example of the actual do if statement. The code works, or appears to work ok, but I can't help but wonder there's a better or more efficient way to deal with the missing values and the do if statement.  Is the missflag variables the only way to handle this?

Thanks
Jeff

new file.
data list FREE/servicedate (adate) effectivedate1 (adate) effectivedate2 (adate) effectivedate3 (adate) max1 (f8.2) max2 (f8.2) max3 (f8.2).
begin data.
12/2/2000       1/1/2000        6/1/2000        12/1/2000       10      20      30
2/15/2000       2/1/2000        .       .       11      .       .
5/15/2000       3/1/2000        5/1/2000        .       15      18      .
4/15/2001       4/1/2000        6/1/2001        .       22      25      .
end data.

compute missflag2=0.
compute missflag3=0.
EXECUTE.

missing values EffectiveDate2(low thru 1000).
if missing(EffectiveDate2) missflag2=1.
EXECUTE.
missing values EffectiveDate3(low thru 1000).
if missing(EffectiveDate3) missflag3=1.
EXECUTE.

Do if (missflag2=1 and missflag3 =1).
compute  MaxAmt = Max1.
Else if (missflag3 =1 and missflag2 ne 1 and ServiceDate ge EffectiveDate1 and ServiceDate lt EffectiveDate2).
compute  MaxAmt = Max1.
Else if (missflag3 =1 and missflag2 ne 1 and ServiceDate ge EffectiveDate2).
compute  MaxAmt = Max2.
Else if (missflag3 ne 1 and missflag2 ne 1 and ServiceDate ge EffectiveDate1 and ServiceDate lt EffectiveDate2).
compute  MaxAmt = Max1.
Else if (missflag3 ne 1 and missflag2 ne 1and ServiceDate ge EffectiveDate2 and ServiceDate lt EffectiveDate3).
compute  MaxAmt = Max2.
Else if (missflag3 ne 1 and missflag2 ne 1and ServiceDate ge EffectiveDate3).
compute  MaxAmt = Max3.
END IF.
EXECUTE.


*original do if code.
Do if (missflag2=1 and missflag3 =1).
compute  MedicaidMax = MedicaidMax1.
compute Maxunits = MaxUnits1.
compute DollarLimit =  DollarLimit1.
compute MonthFreq =  Frequency1.
Else if (missflag3 =1 and missflag2 ne 1 and ServiceDate ge EffectiveDate1 and ServiceDate lt EffectiveDate2).
compute  MedicaidMax = MedicaidMax1.
compute Maxunits = MaxUnits1.
compute DollarLimit =  DollarLimit1.
compute MonthFreq =  Frequency1.
Else if (missflag3 =1 and missflag2 ne 1 and ServiceDate ge EffectiveDate2).
compute  MedicaidMax = MedicaidMax2.
compute Maxunits = MaxUnits2.
compute DollarLimit =  DollarLimit2.
compute MonthFreq =  Frequency2.
Else if (missflag3 ne 1 and missflag2 ne 1 and ServiceDate ge EffectiveDate1 and ServiceDate lt EffectiveDate2).
compute  MedicaidMax = MedicaidMax1.
compute Maxunits = MaxUnits1.
compute DollarLimit =  DollarLimit1.
compute MonthFreq =  Frequency1.
Else if (missflag3 ne 1 and missflag2 ne 1and ServiceDate ge EffectiveDate2 and ServiceDate lt EffectiveDate3).
compute  MedicaidMax = MedicaidMax2.
compute Maxunits = MaxUnits2.
compute DollarLimit =  DollarLimit2.
compute MonthFreq =  Frequency2.
Else if (missflag3 ne 1 and missflag2 ne 1and ServiceDate ge EffectiveDate3).
compute  MedicaidMax = MedicaidMax3.
compute Maxunits = MaxUnits3.
compute DollarLimit =  DollarLimit3.
compute MonthFreq =  Frequency3.
END IF.
EXECUTE.


This e-mail message, including any attachments, is for the sole use of the intended recipient(s) and may contain private, confidential, and/or privileged information. Any unauthorized review, use, disclosure, or distribution is prohibited. If you are not the intended recipient, employee, or agent responsible for delivering this message, please contact the sender by reply e-mail and destroy all copies of the original e-mail message.

=====================
To manage your subscription to SPSSX-L, send a message to
[hidden email] (not to SPSSX-L), with no body text except the
command. To leave the list, send the command
SIGNOFF SPSSX-L
For a list of commands to manage subscriptions, send the command
INFO REFCARD

[Vlad Lopez]

Hi Jeff,

If the cells with formulas in excel give valid dates, convert all the formulas to values

(ctr-a + ctr-c + alt-e-s-v + enter)  then use spss. Hope this help,

 

blad

On Thu, Jul 15, 2010 at 9:10 AM, JEFFREY CANTER <[hidden email]> wrote:

All,

I have a project where I'm trying to determine if a date falls within one of three ranges.  Part of the problem is that the data is coming in from an Excel spreadsheet with some missing values.  The blank cells have formulas in the cells which is causing me to use the missing values code below.  The actual data has four fields per effective range so I trimmed the sample data down to make if easier to look at.  At the bottom  is an example of the actual do if statement. The code works, or appears to work ok, but I can't help but wonder there's a better or more efficient way to deal with the missing values and the do if statement.  Is the missflag variables the only way to handle this?

Thanks
Jeff

new file.
data list FREE/servicedate (adate) effectivedate1 (adate) effectivedate2 (adate) effectivedate3 (adate) max1 (f8.2) max2 (f8.2) max3 (f8.2).
begin data.
12/2/2000       1/1/2000        6/1/2000        12/1/2000       10      20      30
2/15/2000       2/1/2000        .       .       11      .       .
5/15/2000       3/1/2000        5/1/2000        .       15      18      .
4/15/2001       4/1/2000        6/1/2001        .       22      25      .
end data.

compute missflag2=0.
compute missflag3=0.
EXECUTE.

missing values EffectiveDate2(low thru 1000).
if missing(EffectiveDate2) missflag2=1.
EXECUTE.
missing values EffectiveDate3(low thru 1000).
if missing(EffectiveDate3) missflag3=1.
EXECUTE.

Do if (missflag2=1 and missflag3 =1).
compute  MaxAmt = Max1.
Else if (missflag3 =1 and missflag2 ne 1 and ServiceDate ge EffectiveDate1 and ServiceDate lt EffectiveDate2).
compute  MaxAmt = Max1.
Else if (missflag3 =1 and missflag2 ne 1 and ServiceDate ge EffectiveDate2).
compute  MaxAmt = Max2.
Else if (missflag3 ne 1 and missflag2 ne 1 and ServiceDate ge EffectiveDate1 and ServiceDate lt EffectiveDate2).
compute  MaxAmt = Max1.
Else if (missflag3 ne 1 and missflag2 ne 1and ServiceDate ge EffectiveDate2 and ServiceDate lt EffectiveDate3).
compute  MaxAmt = Max2.
Else if (missflag3 ne 1 and missflag2 ne 1and ServiceDate ge EffectiveDate3).
compute  MaxAmt = Max3.
END IF.
EXECUTE.


*original do if code.
Do if (missflag2=1 and missflag3 =1).
compute  MedicaidMax = MedicaidMax1.
compute Maxunits = MaxUnits1.
compute DollarLimit =  DollarLimit1.
compute MonthFreq =  Frequency1.
Else if (missflag3 =1 and missflag2 ne 1 and ServiceDate ge EffectiveDate1 and ServiceDate lt EffectiveDate2).
compute  MedicaidMax = MedicaidMax1.
compute Maxunits = MaxUnits1.
compute DollarLimit =  DollarLimit1.
compute MonthFreq =  Frequency1.
Else if (missflag3 =1 and missflag2 ne 1 and ServiceDate ge EffectiveDate2).
compute  MedicaidMax = MedicaidMax2.
compute Maxunits = MaxUnits2.
compute DollarLimit =  DollarLimit2.
compute MonthFreq =  Frequency2.
Else if (missflag3 ne 1 and missflag2 ne 1 and ServiceDate ge EffectiveDate1 and ServiceDate lt EffectiveDate2).
compute  MedicaidMax = MedicaidMax1.
compute Maxunits = MaxUnits1.
compute DollarLimit =  DollarLimit1.
compute MonthFreq =  Frequency1.
Else if (missflag3 ne 1 and missflag2 ne 1and ServiceDate ge EffectiveDate2 and ServiceDate lt EffectiveDate3).
compute  MedicaidMax = MedicaidMax2.
compute Maxunits = MaxUnits2.
compute DollarLimit =  DollarLimit2.
compute MonthFreq =  Frequency2.
Else if (missflag3 ne 1 and missflag2 ne 1and ServiceDate ge EffectiveDate3).
compute  MedicaidMax = MedicaidMax3.
compute Maxunits = MaxUnits3.
compute DollarLimit =  DollarLimit3.
compute MonthFreq =  Frequency3.
END IF.
EXECUTE.


This e-mail message, including any attachments, is for the sole use of the intended recipient(s) and may contain private, confidential, and/or privileged information. Any unauthorized review, use, disclosure, or distribution is prohibited. If you are not the intended recipient, employee, or agent responsible for delivering this message, please contact the sender by reply e-mail and destroy all copies of the original e-mail message.

=====================
To manage your subscription to SPSSX-L, send a message to
[hidden email] (not to SPSSX-L), with no body text except the
command. To leave the list, send the command
SIGNOFF SPSSX-L
For a list of commands to manage subscriptions, send the command
INFO REFCARD

[JEFFREY CANTER]

Vlad,

Thanks for the tip.  I've used this before but forgot about it. Too caught up in the code I guess.  Any suggestions for the Do If code.  The original code at the end works but I'm convinced that it can be improved.

Thanks
Jeff


>>> Vladimir Lopez-Prado <[hidden email]> 7/15/2010 3:42 PM >>>
Hi Jeff,
If the cells with formulas in excel give valid dates, convert all the
formulas to values
(ctr-a + ctr-c + alt-e-s-v + enter)  then use spss. Hope this help,

blad

On Thu, Jul 15, 2010 at 9:10 AM, JEFFREY CANTER <[hidden email]>wrote:

> All,
>
> I have a project where I'm trying to determine if a date falls within one
> of three ranges.  Part of the problem is that the data is coming in from an
> Excel spreadsheet with some missing values.  The blank cells have formulas
> in the cells which is causing me to use the missing values code below.  The
> actual data has four fields per effective range so I trimmed the sample data
> down to make if easier to look at.  At the bottom  is an example of the
> actual do if statement. The code works, or appears to work ok, but I can't
> help but wonder there's a better or more efficient way to deal with the
> missing values and the do if statement.  Is the missflag variables the only
> way to handle this?
>
> Thanks
> Jeff
>
> new file.
> data list FREE/servicedate (adate) effectivedate1 (adate) effectivedate2
> (adate) effectivedate3 (adate) max1 (f8.2) max2 (f8.2) max3 (f8.2).
> begin data.
> 12/2/2000       1/1/2000        6/1/2000        12/1/2000       10      20
>      30
> 2/15/2000       2/1/2000        .       .       11      .       .
> 5/15/2000       3/1/2000        5/1/2000        .       15      18      .
> 4/15/2001       4/1/2000        6/1/2001        .       22      25      .
> end data.
>
> compute missflag2=0.
> compute missflag3=0.
> EXECUTE.
>
> missing values EffectiveDate2(low thru 1000).
> if missing(EffectiveDate2) missflag2=1.
> EXECUTE.
> missing values EffectiveDate3(low thru 1000).
> if missing(EffectiveDate3) missflag3=1.
> EXECUTE.
>
> Do if (missflag2=1 and missflag3 =1).
> compute  MaxAmt = Max1.
> Else if (missflag3 =1 and missflag2 ne 1 and ServiceDate ge EffectiveDate1
> and ServiceDate lt EffectiveDate2).
> compute  MaxAmt = Max1.
> Else if (missflag3 =1 and missflag2 ne 1 and ServiceDate ge
> EffectiveDate2).
> compute  MaxAmt = Max2.
> Else if (missflag3 ne 1 and missflag2 ne 1 and ServiceDate ge
> EffectiveDate1 and ServiceDate lt EffectiveDate2).
> compute  MaxAmt = Max1.
> Else if (missflag3 ne 1 and missflag2 ne 1and ServiceDate ge EffectiveDate2
> and ServiceDate lt EffectiveDate3).
> compute  MaxAmt = Max2.
> Else if (missflag3 ne 1 and missflag2 ne 1and ServiceDate ge
> EffectiveDate3).
> compute  MaxAmt = Max3.
> END IF.
> EXECUTE.
>
>
> *original do if code.
> Do if (missflag2=1 and missflag3 =1).
> compute  MedicaidMax = MedicaidMax1.
> compute Maxunits = MaxUnits1.
> compute DollarLimit =  DollarLimit1.
> compute MonthFreq =  Frequency1.
> Else if (missflag3 =1 and missflag2 ne 1 and ServiceDate ge EffectiveDate1
> and ServiceDate lt EffectiveDate2).
> compute  MedicaidMax = MedicaidMax1.
> compute Maxunits = MaxUnits1.
> compute DollarLimit =  DollarLimit1.
> compute MonthFreq =  Frequency1.
> Else if (missflag3 =1 and missflag2 ne 1 and ServiceDate ge
> EffectiveDate2).
> compute  MedicaidMax = MedicaidMax2.
> compute Maxunits = MaxUnits2.
> compute DollarLimit =  DollarLimit2.
> compute MonthFreq =  Frequency2.
> Else if (missflag3 ne 1 and missflag2 ne 1 and ServiceDate ge
> EffectiveDate1 and ServiceDate lt EffectiveDate2).
> compute  MedicaidMax = MedicaidMax1.
> compute Maxunits = MaxUnits1.
> compute DollarLimit =  DollarLimit1.
> compute MonthFreq =  Frequency1.
> Else if (missflag3 ne 1 and missflag2 ne 1and ServiceDate ge EffectiveDate2
> and ServiceDate lt EffectiveDate3).
> compute  MedicaidMax = MedicaidMax2.
> compute Maxunits = MaxUnits2.
> compute DollarLimit =  DollarLimit2.
> compute MonthFreq =  Frequency2.
> Else if (missflag3 ne 1 and missflag2 ne 1and ServiceDate ge
> EffectiveDate3).
> compute  MedicaidMax = MedicaidMax3.
> compute Maxunits = MaxUnits3.
> compute DollarLimit =  DollarLimit3.
> compute MonthFreq =  Frequency3.
> END IF.
> EXECUTE.
>
>
> This e-mail message, including any attachments, is for the sole use of the
> intended recipient(s) and may contain private, confidential, and/or
> privileged information. Any unauthorized review, use, disclosure, or
> distribution is prohibited. If you are not the intended recipient, employee,
> or agent responsible for delivering this message, please contact the sender
> by reply e-mail and destroy all copies of the original e-mail message.
>
> =====================
> To manage your subscription to SPSSX-L, send a message to
> [hidden email] (not to SPSSX-L), with no body text except the
> command. To leave the list, send the command
> SIGNOFF SPSSX-L
> For a list of commands to manage subscriptions, send the command
> INFO REFCARD
>

This e-mail message, including any attachments, is for the sole use of the intended recipient(s) and may contain private, confidential, and/or privileged information. Any unauthorized review, use, disclosure, or distribution is prohibited. If you are not the intended recipient, employee, or agent responsible for delivering this message, please contact the sender by reply e-mail and destroy all copies of the original e-mail message.

=====================
To manage your subscription to SPSSX-L, send a message to
[hidden email] (not to SPSSX-L), with no body text except the
command. To leave the list, send the command
SIGNOFF SPSSX-L
For a list of commands to manage subscriptions, send the command
INFO REFCARD