09-06-2011 04:38 PM
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?
09-06-2011 05:43 PM
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.
09-06-2011 10:38 PM
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.
09-07-2011 04:05 AM
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
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.
09-07-2011 12:08 PM
Best way? Here's one (probably ugly) way...
input custno purchdate;
informat purchdate mmddyy10.;
format purchdate mmddyy10.;
proc sort data=pannel out=temp1;
by custno purchdate;
proc transpose data=temp1 out=temp2 let prefix=Y;
format purchdate yymm5.;
select t1.*,case when t2.x=0 then 1 else 0 end as evrymnth from
pannel as t1
temp3 as t2