BookmarkSubscribeRSS Feed
CesarOmarHR
Calcite | Level 5

I have dates by ClientID with a variable number of rows per ID, regarding purchases. I would like to have a variable containing the oldest date of purchase per ClientID with the previous repeating itself through the same ClientID's variable rows. I'm preparing the data for future summarizing by ClientID processes. The green variable is what i am aiming for.

clientID               date                         FirstPurchase

111114329          14JUL2006                14JUL2006

111114329           16JUL2006               14JUL2006

111114329           01AUG2006              14JUL2006

111114329           07AUG2006              14JUL2006

111114329           16AUG2006              14JUL2006

111114329           21AUG2006              14JUL2006

111114329           03SEP2006               14JUL2006

153495806           30SEP2008               30SEP2008

153495806           11OCT2008              30SEP2008

153495806           01NOV2008              30SEP2008     

153495806           11NOV2008               30SEP2008

153495806           16NOV2008               30SEP2008

153495806           02DEC2008               30SEP2008

153495806           13DEC2008               30SEP2008

153495806           02JAN2009               30SEP2008

153981795           31SEP2008               31SEP2008

153981795           08NOV2008               31SEP2008

153981795           14NOV2008               31SEP2008

153981795           28NOV2008               31SEP2008

153981795           12DEC2008               31SEP2008

153981795           05JAN2009                31SEP2008    

What i've done succesfully so far in Guide are the following colored statements:

CASE

   WHEN t1.clientID = t1.clientID THEN min(t1.date)    ....      (Another condition)

  ELSE t1.clientID

END

What complementary command do i use for Guide to understand that he should search in every clientID's for the oldest date?

Thanks, i'll appreciate the help.

Cesar Hernandez

2 REPLIES 2
Haikuo
Onyx | Level 15

There will be many ways to solve your problem, if you are keen on SQL, here is one approach:

proc sql noprint;

  create table want as

    select *, min(date) as purchsedate format=date9. from have

    group by clientid;

    quit;

data step as an alternative:

   data want;

     set have (in=up) have;

       by clientid;

       retain purchasedate;

       format purchasedate date9.;

       if first.clientid then call missing(purchasedate);

        if up then purchasedate=min(date,purchasedate);

        if ^up then output;

    run;

Haikuo

CesarOmarHR
Calcite | Level 5

Thanks, it all worked fine

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!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

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
  • 2 replies
  • 909 views
  • 4 likes
  • 2 in conversation