SAS Programming

DATA Step, Macro, Functions and more
BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
epr
Calcite | Level 5 epr
Calcite | Level 5

Hi All,

I need some help formatting dates.

data date_duration13;

set c6;

   by id;

    retain first_date;

   if first.id then first_date = date ;

   informat date ddmmyy10.;

   format date ddmmyy10.;

   if last.id then do;

     last_date = date ;

  informat date ddmmyy10.;

  format date ddmmyy10.;

  

     duration =last_date-first_date ;

     output;

   end;

  

run;

This is the code i used between first date date last date.

Obs id         date               price           category                    first_date           last_date       duration

1   9801134 12/11/1999 14.11      books                                  14559     14560                    1

2    9801234 12/01/1998 130.98     books                                13889      13891                     2

3    9802234 11/10/1997  100       apparel                                13799     13798                         -1

4  9805556 10/11/1999  18.9       books                                   13187     14558                          1371

I would like to see first_date,lase_date in dateformat like ddmmyy and also like to calculate average no of days between purchases.

1 ACCEPTED SOLUTION

Accepted Solutions
DBailey
Lapis Lazuli | Level 10

something like this?

data date_duration13;

set c6;

     by id;

     format

          first_date ddmmyy10.

          last_date ddmmyy10.

          orders 10.

          Tot_Duration 10.

     ;

     retain

          first_date

          orders

          Tot_Duration

          tmpOrderDate

     ;

     if first.id then do;

          first_date = date;

          orders=1;

          Tot_Duration=0;

          tmpOrderDate = date;

          end;

else do;

     orders+1;

     Tot_Duration = Tot_Duration + date - tmpOrderDate;

     tmpOrderDate=date;

     end;

     if last.id then do;

          last_date = date ;

          duration =last_date-first_date ;

          AvgDuration = Tot_Duration / orders;

          output;

     end;

run;

View solution in original post

4 REPLIES 4
Reeza
Super User

Add format statements for your first_date and last_date variables.

e.g.

format first_date ddmmyy10.;

DBailey
Lapis Lazuli | Level 10

something like this?

data date_duration13;

set c6;

     by id;

     format

          first_date ddmmyy10.

          last_date ddmmyy10.

          orders 10.

          Tot_Duration 10.

     ;

     retain

          first_date

          orders

          Tot_Duration

          tmpOrderDate

     ;

     if first.id then do;

          first_date = date;

          orders=1;

          Tot_Duration=0;

          tmpOrderDate = date;

          end;

else do;

     orders+1;

     Tot_Duration = Tot_Duration + date - tmpOrderDate;

     tmpOrderDate=date;

     end;

     if last.id then do;

          last_date = date ;

          duration =last_date-first_date ;

          AvgDuration = Tot_Duration / orders;

          output;

     end;

run;

Astounding
PROC Star

Good thinking, and very close.  Remember, the number of durations is ORDERS-1, not ORDERS.  In that case, you might have to check for there being only one order for an ID as well.

epr
Calcite | Level 5 epr
Calcite | Level 5

Thank you all for your input

I got right output.

sas-innovate-wordmark-2025-midnight.png

Register Today!

Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.


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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 4 replies
  • 1305 views
  • 6 likes
  • 4 in conversation