[prev in list] [next in list] [prev in thread] [next in thread] 

List:       sas-l
Subject:    Re: create a new variable to track last purchase
From:       Roger Lustig <trovato () BELLATLANTIC ! NET>
Date:       2002-12-30 20:52:56
[Download RAW message or body]

Folks:

For complete avoidance of lag(), something that generally saves me a lot
of headaches (YMMV), you might try the following, assuming that your raw
data is already in a SAS data set or equivalent:

data new;
set old;
by hhid; *** Use NOTSORTED option if needed;
retain lbrand;
if first.hhid and brand=. then do;
     <assign lbrand at random>
     end;
else if brand ne . then lbrand=brand;
run;

Best,

Roger Lustig


Ian Whitlock wrote:
> Larry,
>
> Sometimes it is easier to think in terms of RETAIN then LAG.  I think this
> is one of them.  As a general answer where you might want missing when there
> was no previous purchase consider
>
> data q ;
>    infile cards missover dsd ;
>    input hhid week day store brand ;
>    retain lbrand ;
>    if hhid ^= lag ( hhid ) then lbrand = . ;
>    output ;
>    if not missing ( brand ) then lbrand = brand ;
> cards ;
> <data>
> ;
>
> The plan is simple.  Start off with missing and assign LBRAND after the
> OUTPUT statement so it affects the next record and not the current one.
>
> Your example shows a value 2 for HHID=1 when there is nothing in your data
> to support it and and a value 1 for HHID=2.  If this sort of thing is really
> needed then you could use a jazzed up version of the above.  Since I have no
> idea where the initial values are coming from, I leave it to you to populate
> a temporary array with the required values.  If you specify the source in
> more detail perhaps a better solution would be available.
>
> data w ;
>    infile cards missover dsd ;
>    input hhid week day store brand ;
>    retain lbrand ;
>
>    array temp (10) _temporary_ ( 10*. ) ;
>    if hhid ^= lag (hhid) then
>    do ;
>       i + 1 ;
>       if i > dim ( temp ) then error "i > dimension of temp" ;
>       lbrand = temp[i] ;
>    end ;
>    output ;
>    if not missing ( brand ) then lbrand =  brand ;
> cards ;
> <data>
> ;
>
> IanWhitlock@westat.com
>
> -----Original Message-----
> From: Larry Che [mailto:che@OLIN.WUSTL.EDU]
> Sent: Saturday, December 28, 2002 3:48 PM
> To: SAS-L@LISTSERV.UGA.EDU
> Subject: Re: create a new variable to track last purchase
>
>
> Dear all,
>
> I have a dataset which looks like follows,
>
> hhid,week,day,store,brand
> 1,640,3,3,
> 1,642,3,3,
> 1,643,4,3,
> 1,645,4,3,
> 1,647,1,3,
> 1,647,4,3,30
> 1,649,1,3,
> 1,649,4,3,
> 1,650,1,3,32
> 1,651,4,3,
> 1,652,1,3,
> 2,612,4,3,
> 2,613,1,3,
> 2,614,1,3,
> 2,614,4,3,64
> 2,625,1,3,
> 2,645,3,3,
> 2,656,1,3,
> 2,656,4,3,90
>
> for these 2 households, they make shopping trips, each trip, they could buy
> a certain brand, or not buy anything at all.
>
> I want to create a dataset which tracks the household's last purchase, even
> for the no-purchase shopping trips.  So if the household did not buy a brand
> that trip, I want to create a new variable called lbrand (i.e. lagged brand)
> to track his last purchase brand, which could be purchase many trips ago.
>
> One more thing is, if the household did not buy in his FIRST shopping trip,
> then I want to make a random draw from a multinomial distribution (let's say
> 3 brands, each with a prob of 0.33).
>
> So the above dataset I want to transform it to,
>
> hhid,week,day,store,brand,lbrand
> 1,640,3,3,,2
> 1,642,3,3,,2
> 1,643,4,3,,2
> 1,645,4,3,,2
> 1,647,1,3,,2
> 1,647,4,3,30,2
> 1,649,1,3,,30
> 1,649,4,3,,30
> 1,650,1,3,32,30
> 1,651,4,3,,32
> 1,652,1,3,,32
> 2,612,4,3,,1
> 2,613,1,3,,1
> 2,614,1,3,,1
> 2,614,4,3,64,1
> 2,625,1,3,,64
> 2,645,3,3,,64
> 2,656,1,3,,64
> 2,656,4,3,90,64
>
> I tried using lag() but did not succeed.  I am also not sure how to track
> the last purchased brand which did not happen in the last observation.
>
> Hope the experts here could help me with this problem.  Many thanks,
>
> Larry

[prev in list] [next in list] [prev in thread] [next in thread]