topic Re: re: Data Selection in SAS Programming

re: Data Selection

twildone — Mon, 17 Aug 2015 14:11:19 GMT

Hi....I am trying to determine whether or not a client is a new customer or repeat customer based on whether the customer had purchased at least one (1) product on their current purchsed from a previous purchase. The data set looks like this:

Client_ID Product_Number Date

37543 00653 20130403

37543 00345 20130403

37543 00765 20130403

37543 00653 20130812

37543 00345 20130812

37543 00765 20130812

37543 00831 20130812

37543 00765 20131222

37543 00949 20131222

And I was hoping to have dataset as:

Client_ID Date Type

37543 20130403 New

37543 20130812 Repeat

37543 20131222 Repeat

Any suggestions would bee greatly appreciated. Thanks

Re: re: Data Selection

ndp — Mon, 17 Aug 2015 14:19:35 GMT

proc sort data=... nodupkey;

by Client_ID Date;

run;

data ...;

set ...;

by Client_ID Date;

length type $6;

if first.client_id then type="New";

else type="Repeat";

run;

Re: re: Data Selection

RW9 — Mon, 17 Aug 2015 14:23:25 GMT

You may want to just add an output so the final dataset only has one record per group:

data ...;

set ...;

by Client_ID Date;

length type $6;

if first.client_id then type="New";

else type="Repeat";

if last.client_id then output;

run;

Re: re: Data Selection

twildone — Mon, 17 Aug 2015 14:43:30 GMT

Hi...Thanks for the quick response. I should have mentioned that it is possible that the client may not have purchased a product that they had previous purchased and in this case they would be considered "New" even though they made purchases before. That is why I need to check to see if the client had purchased any of the items in their previous purchases. This is the part that I am having difficulty with. Thanks

Re: re: Data Selection

ndp — Mon, 17 Aug 2015 14:59:38 GMT

I am not sure i follow your algorithm please provide appropriate example.

Re: re: Data Selection

MikeZdeb — Mon, 17 Aug 2015 15:09:58 GMT

Hi. Try this (there is probably a hash approach with a little less code, but I understand this). I added a second client with slightly different purchases to show how the code behaves when you get a new client (I think it works) ...

data x;

input client product date : yymmdd.;

format date mmddyy10.;

datalines;

37543 00653 20130403

37543 00345 20130403

37543 00765 20130403

37543 00653 20130812

37543 00345 20130812

37543 00765 20130812

37543 00831 20130812

37543 00765 20131222

37543 00949 20131222

97543 00653 20130403

97543 00345 20130812

97543 00765 20130812

97543 00831 20130812

97543 00765 20131222

97543 00949 20131222

;

data y (keep=client date type);

length type $6;

* keep track of products bought within a client;

array bought(30) _temporary_ (30*0);

do until (last.client);

* assume a NEW type;

type = 'new';

do until (last.date);

set x;

by client date;

* check if product within each date is in the array (yes/change type to repeat, no/add to array);

if product in bought then type = 'repeat';

else do; j+1; bought(j) = product; end;

end;

output;

end;

* clear array and counter for each new client;

do i = 1 to 30; bought(i) = 0; end;

j=0;

run;

DATA SET Y ...

Obs type client date

1 new 37543 04/03/2013

2 repeat 37543 08/12/2013

3 repeat 37543 12/22/2013

4 new 97543 04/03/2013

5 new 97543 08/12/2013

6 repeat 97543 12/22/2013

Re: re: Data Selection

twildone — Mon, 17 Aug 2015 15:37:43 GMT

Thanks MikeZdeb....it works perfect...exactly what I needed....thanks to everyone who made suggestions as well.

Re: re: Data Selection

MikeZdeb — Mon, 17 Aug 2015 16:01:23 GMT

Hi ... another thought that eliminates the array (since you can concatenate numeric variables) ...

data y (keep=client date type);

length type $6 bought $100;

do until (last.client);

type = 'new';

do until (last.date);

set x;

by client date;

* CAT(PRODUCT) gets rid of warning message in the LOG about data type conversion;

if findw(bought,cat(product)) then type = 'repeat';

else bought = catx('/',bought,product);

end;

output;

end;

call missing (bought);

run;

Re: re: Data Selection

twildone — Mon, 17 Aug 2015 17:23:07 GMT

Hi MikeZdeb...thanks for taking the time to respond to my request. I am getting an eror message (

ERROR: Array subscript out of range at line 187 column 4.)....From the log, it seems there is a problem with " bought(j) = product" statement. Any suggestion why this is happening?

Re: re: Data Selection

MikeZdeb — Mon, 17 Aug 2015 19:48:34 GMT

Hi. I wrote the code assuming 30 products. Easy to change, find ...

array bought(30) _temporary_ (30*0);

change to ...

array bought(100) _temporary_ (30*0);

then change ...

do i = 1 to 30; bought(i) = 0; end;

do i = 1 to 100; bought(i) = 0; end;

If you get that message again, make the array (and loop) larger.

Re: re: Data Selection

MikeZdeb — Mon, 17 Aug 2015 20:25:23 GMT

ps If you use the other solution I posted that has no array, you could change this line ...

length type $6 bought $100;

to ...

length type $6 bought $1000;

to accommodate more product numbers in the variable BOUGHT.

Re: re: Data Selection

Ksharp — Tue, 18 Aug 2015 13:13:08 GMT

Code: Program

data x;
input client product date : yymmdd.;
format date mmddyy10.;
datalines;
37543 00653 20130403
37543 00345 20130403
37543 00765 20130403
37543 00653 20130812
37543 00345 20130812
37543 00765 20130812
37543 00831 20130812
37543 00765 20131222
37543 00949 20131222
97543 00653 20130403
97543 00345 20130812
97543 00765 20130812
97543 00831 20130812
97543 00765 20131222
97543 00949 20131222
;
run;
data want;
if _n_ eq 1 then do;
 if 0 then set x;
 declare hash ha();
 ha.definekey('product');
 ha.definedone();
end;
 set x;
 by client date ;
 retain type 'new ';
 if first.client then ha.clear();
 if first.date then type='new';
 if ha.check()=0 then type='repeat';
 ha.replace();
 if last.date;
 drop product ;
run;

Re: re: Data Selection

Ksharp — Tue, 18 Aug 2015 13:14:32 GMT

A problem is if there are missing value in PRODUCT, what you gotta do ?

Re: re: Data Selection

twildone — Tue, 18 Aug 2015 15:16:20 GMT

Hi Xia Keshan, I don't have to worry about a missing value for Product as that field is mandatory and a record would not be created if that field is missing. Thanks for your suggestion and I will try it to se if I get the same results..

Re: re: Data Selection

twildone — Tue, 18 Aug 2015 15:19:59 GMT

Hi MikeZdeb.....Thanks for your suggestions. I made the changes as you had suggested and your suggestion with the array and loop worked perfectly. The suggestion without the array after making the corrections as suggested, seemed to give slightly different results when I compared the results from both approaches.

Re: re: Data Selection

MikeZdeb — Wed, 19 Aug 2015 15:09:47 GMT

Hi. Send me a bit of data that produced the different results and I'll figure it out (msz03@albany.edu).