BookmarkSubscribeRSS Feed
Danglytics
Calcite | Level 5

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.

4 REPLIES 4
art297
Opal | Level 21

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
Calcite | Level 5

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
Calcite | Level 5

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
Opal | Level 21

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 ;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 4 replies
  • 783 views
  • 0 likes
  • 2 in conversation