data logic

[Danglytics]

Hi,

I have a question regarding logic to my dataset.

an example of my data is:

id bill_cycle bill_st_date bill_nd_date action action_date

1 26 01/26/2011 02/27/2011 open 01/26/2011

1 26 . . closed 02/24/2011

1 26 02/26/2011 03/27/2011 open 02/26/2011

etc.

I have certain transactions in my data that do not have a bill_st_date, or bill_nd_date, i want to assign that transaction to bill_st_date and bill_nd_date for the action date that the transaction falls into.

the bill_cycle will always be the bill_st_date's day.

so i'd like it to look like:

id bill_cycle bill_st_date bill_nd_date action action_date

1 26 01/26/2011 02/27/2011 open 01/01/2011

1 26 01/26/2011 02/27/2011 closed 02/24/2011

1 26 02/26/2011 03/27/2011 open 02/26/2011

Thanks for your help.

[art297]

If your data is in time and id-sequential order, or if you can make it so, your question is easy to solve.  E.g., if the only missing (to be filled in values) for bill_st_date, you could (in a datastep) create a variable (e.g., hold_bill_st_date), retain it, only change it if it isn't missing and, if it is missing, assign the value of hold_bill_st_date to it.

Would doing something like the above solve your problem?

[Danglytics]

Thanks for the quick reply.

i'll give it a try, but my gut feeling is it wont work with this data set, there might be a few exceptions..

[Danglytics]

i'm not sure if i'm following the holding logic correctly

data work.example_new ; set work.example;

retain hold_bill_st_date hold_bill_nd_date ;

hold_bill_st_date = bill_st_date ;

hold_bill_nd_date = bill_nd_date ;

if bill_nd_date = . then bill_nd_date = hold_bill_nd_date ;

if bill_st_date = . then bill_st_date = hold_bill_st_date ;

run ;

i dont think the above is working correctly..

[art297]

Without sample data I can't test it.  But, that said, I think you might want something like:

data work.example_new ;

  set work.example;

  retain hold_bill_st_date hold_bill_nd_date ;

  if bill_st_date ne . then hold_bill_st_date = bill_st_date ;

  else bill_sd_date = hold_bill_sd_date ;

  if bill_nd_date ne . then hold_bill_nt_date = bill_nt_date ;

  else bill_nd_date = hold_bill_nd_date ;

run ;