topic Dropping observations by ID and variable range in SAS Procedures

Dropping observations by ID and variable range

randyl — Thu, 16 Feb 2012 22:49:16 GMT

Hi all,

First time poster (very new to SAS).

I'm running an event study and I have a large dataset. I'm trying to include only those company's (ID) that have data from -5 <= time <= 10... but I noticed that there many companies in the dataset that have missing data in the time range. For example, in the tables below, company ID 001 satisfies the requirement while company ID 150 has missing data in the time range.

How am I able to scrap the companies in the dataset with missing data in -5 <= time <= 10 ?

Thanks kindly! My apologies if this question is trivial.

ID	time	price
001	-5	6.5
001	-4	6.9
001	-3	7.1
001	-2	7.23
001	-1	7.12
001	0	8.02
001	1	8.55
001	2	8.49
001	3	8.52
001	4	8.69
001	5	8.75
001	6	8.65
001	7	8.6
001	8	8.66
001	9	8.7
001	10	8.72

ID	time	price
150	-5	.
150	-4	.
150	-3	.
150	-2	.
150	-1	.
150	0	22.5
150	1	26.88
150	2	27.16
150	3	27.05
150	4	27.22
150	5	29
150	6	28.64
150	7	28.12
150	8	28.01
150	9	28.29
150	10	28.55

Dropping observations by ID and variable range

art297 — Thu, 16 Feb 2012 22:54:24 GMT

Do you just want to get rid of records that have missing price data, or entire companies if they have any missing data that happens to be in your designated range?

Dropping observations by ID and variable range

randyl — Thu, 16 Feb 2012 23:00:15 GMT

Hello Art,

I would like to get rid of all the companies that have missing price data in the designated range. In the tables above, I would like to drop Company ID 150 from the dataset.

Thanks!

Randy

Dropping observations by ID and variable range

Tom — Thu, 16 Feb 2012 23:09:54 GMT

The simplest is to merge it back with itself.

data want ;

merge have (in=in1 where=(price=.) keep=id price) have ;

by id ;

if in1 then delete;

run;

This works when the records with missing values for price are actually in the dataset. If you had cases where the records for the missing prices are just not in the dataset then it will not work.

Dropping observations by ID and variable range

art297 — Thu, 16 Feb 2012 23:09:55 GMT

One way would be to use proc sql. You would have to test it, of course, but I think that the following meets your requirements:

proc sql;

create table want as

select *

from have

group by id

having sum(missing(price)) eq 0 and

time between -5 and 10

;

quit;

Dropping observations by ID and variable range

Haikuo — Thu, 16 Feb 2012 23:56:27 GMT

Or a 2x DWL if presorted by ID,

data want (drop=flag);

do until (last.id);

set have;

by id;

if -5<=time<=10;

if missing(price) then flag=1;

end;

do until (last.id);

set have;

by id;

if -5<=time<=10;

if flag ne 1 then output;

end;

run;

Regards,

Haikuo

Dropping observations by ID and variable range

art297 — Fri, 17 Feb 2012 00:06:34 GMT

Haikuo, I'd send this to you, personally, but I don't know your email address. If you want to share it you can write me at atabachneck at gmail dot com.

Just so we don't start introducing even more acronymns, I think the one you are suggesting is called a double DOW loop.

The term was coined on SAS-L, and in some SUGI/SGF papers, but I think it primarily refers to two of its principal inventors, namely Ian Whitlock and Paul Dorfman. One of the discussion forum's own members, Howles (who also happens to be a SAS-L Hall of Fame member along with Ian and Paul), also played a significant role, but I don't believe that his name was ever incorporated into the acronym.

Dropping observations by ID and variable range

Haikuo — Fri, 17 Feb 2012 02:13:18 GMT

Art,

Thank you for correcting me. I guess I took it for granted that the "Do While Loop" for DWL, and "Do Until Loop" as DUL. Apparently it is all wrong. And I have no intention what so ever of disrespecting or discrediting Ian ,Paul and Howles.

I am actually glad you did it in public so my error will not spread.

I appreciate you left your email, which I will surely use to bug you in the future.

In case Ian, Paul or Howles come cross this post, Please accept my sincere apology.

Haikuo

Dropping observations by ID and variable range

Ksharp — Fri, 17 Feb 2012 07:11:41 GMT

Tom.

If OP's data has some time less than -5 or greater than 10, your code can't work , because OP want data between -5 and 10.

Ksharp