topic Re: data cleaning in SAS Programming

data cleaning

Amalik — Wed, 18 Jul 2018 01:19:59 GMT

Hi,

My data looks like follows. Now, I want to delete a month if any month has say less than 20 observations. Can anyone suggest how to do that?

Re: data cleaning

pau13rown — Wed, 18 Jul 2018 01:27:30 GMT

proc sort data=a;

by month;

run;

data b;

retain count;

set a;

by month;

if first.month then count=1;

else count=count+1;

if last.month then output;

keep month count;

run;

data c;

merge b a;

by month;

if count lt 20 then delete;

run;

Re: data cleaning

Ksharp — Wed, 18 Jul 2018 13:21:07 GMT

proc sql;

create table want as

select *

from have

group by permno,month

having count(*)>20;

quit;

Re: data cleaning

Amalik — Fri, 20 Jul 2018 02:57:25 GMT

Hi Ksharp,

This is very helpful and resolves my question but can you also let me know how to obtain a data set if for example, the variable DR (second last from right) shows all values to be zero, or sometimes it would show only one value that is different than 0 and all others would be zero. So I would like to delete a month if the frequency of 0 is 19 or greater in any given month.

Re: data cleaning

s_manoj — Fri, 20 Jul 2018 06:38:22 GMT

proc sql;
create table want as
select *
from have
group by month,dr
having dr = 0 and count(dr) < 19;
quit;

I havent tested this code, but i think this might work

Regards
Manoj

Re: data cleaning

Ksharp — Sat, 21 Jul 2018 11:01:55 GMT

If I understood your question.

Change HAVING clause into :

having sum(dc=0)<19;