How to compute a recoded column with the MIN function?

CesarOmarHR — Tue, 09 Oct 2012 22:18:54 GMT

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

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

Haikuo — Wed, 10 Oct 2012 01:02:07 GMT

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

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

CesarOmarHR — Wed, 10 Oct 2012 13:51:19 GMT

Thanks, it all worked fine

topic Re: How to compute a recoded column with the MIN function? in SAS Enterprise Guide

How to compute a recoded column with the MIN function?

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

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