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-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
  • 910 views
  • 6 likes
  • 4 in conversation