BookmarkSubscribeRSS Feed
jkf91
Calcite | Level 5

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

4 REPLIES 4
art297
Opal | Level 21

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.

Ksharp
Super User

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

LinusH
Tourmaline | Level 20

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

Data never sleeps
scmebu
SAS Employee

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;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 4 replies
  • 716 views
  • 0 likes
  • 5 in conversation