Desktop productivity for business analysts and programmers

How to compute a recoded column with the MIN function?

Reply
Contributor
Posts: 36

How to compute a recoded column with the MIN function?

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

Respected Advisor
Posts: 3,156

Re: How to compute a recoded column with the MIN function?

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

Contributor
Posts: 36

Re: How to compute a recoded column with the MIN function?

Thanks, it all worked fine

Ask a Question
Discussion stats
  • 2 replies
  • 236 views
  • 4 likes
  • 2 in conversation