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,
C
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.
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
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
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.