topic only keep consistent observations in SAS Procedures

only keep consistent observations

jkf91 — Tue, 06 Sep 2011 20:38:12 GMT

I have a pannel data wth purchase dates (MMDDYY10.)

I want to identify which customers made a purchase every single month. (a dummy variable that takes a value 1 if purchase was made every month; 0 otherwise)

What is the best way to solve this problem?

Thanks,

only keep consistent observations

art297 — Tue, 06 Sep 2011 21:43:08 GMT

I think that the Forum would need to see a sample of your data, preferably in the form of a datastep. Since you want to select customers who only have 1s in a particular field, I would think that you will want to use proc sql, selecting distinct customer, having min(whateverfield)*max(whateverfield) equalling 1.

only keep consistent observations

Ksharp — Wed, 07 Sep 2011 02:38:31 GMT

As Art.T said. It is hard to judge something without seeing some sample data.

But I think You can use proc freq;tables id*month to identified the count number for each id at each month.

Ksharp

Re: only keep consistent observations

LinusH — Wed, 07 Sep 2011 08:05:28 GMT

First, you have to decide how many months (and which) are "all" months.

If you can simplify it by saying all month this your, you maybe can use an SQL construct like this:

select cust_id, count(distinct(put(date,yymmn6.)) as NoOfMonths

from PannelTable

where date between '01Jan2010'd and '31dec2010'd

group by cust_id

having NoOfMonths eq 12

;

The result can be joined to the customer table to set whatever flag/info you need.

Linus

Re: only keep consistent observations

scmebu — Wed, 07 Sep 2011 16:08:26 GMT

Best way? Here's one (probably ugly) way...

data pannel;

input custno purchdate;

informat purchdate mmddyy10.;

format purchdate mmddyy10.;

cards;

1 01/01/2010

1 02/01/2010

1 03/01/2010

1 04/01/2010

2 01/01/2010

2 02/01/2010

2 04/01/2010

;

run;

proc sort data=pannel out=temp1;

by custno purchdate;

run;

proc transpose data=temp1 out=temp2 let prefix=Y;

format purchdate yymm5.;

by custno;

id purchdate;

var custno;

run;

data temp3;

set temp2;

x=nmiss(of Y:);

run;

proc sql;

select t1.*,case when t2.x=0 then 1 else 0 end as evrymnth from

pannel as t1

left join

temp3 as t2

on t1.custno=t2.custno;

quit;