topic data logic in SAS Programming

data logic

Danglytics — Tue, 10 Jan 2012 23:09:56 GMT

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.

data logic

art297 — Tue, 10 Jan 2012 23:19:43 GMT

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?

data logic

Danglytics — Tue, 10 Jan 2012 23:30:22 GMT

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..

data logic

Danglytics — Tue, 10 Jan 2012 23:51:12 GMT

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..

data logic

art297 — Tue, 10 Jan 2012 23:58:58 GMT

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 ;