Help using Base SAS procedures

only keep consistent observations

Reply
Contributor
Posts: 44

only keep consistent observations

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

PROC Star
Posts: 7,356

only keep consistent observations

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.

Super User
Posts: 9,662

only keep consistent observations

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

Super User
Posts: 5,254

Re: only keep consistent observations

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
SAS Employee
Posts: 17

Re: only keep consistent observations

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 YSmiley Happy;

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;

Ask a Question
Discussion stats
  • 4 replies
  • 128 views
  • 0 likes
  • 5 in conversation