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
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
Thanks, it all worked fine
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.