Help using Base SAS procedures

Dropping observations by ID and variable range

Reply
New Contributor
Posts: 2

Dropping observations by ID and variable range

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.

IDtimeprice
001-56.5
001-46.9
001-37.1
001-27.23
001-17.12
00108.02
00118.55
00128.49
00138.52
00148.69
00158.75
00168.65
00178.6
00188.66
00198.7
001108.72



IDtimeprice
150-5.
150-4.
150-3.
150-2.
150-1.
150022.5
150126.88
150227.16
150327.05
150427.22
150529
150628.64
150728.12
150828.01
150928.29
1501028.55
PROC Star
Posts: 7,468

Dropping observations by ID and variable range

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?

New Contributor
Posts: 2

Dropping observations by ID and variable range

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

PROC Star
Posts: 7,468

Dropping observations by ID and variable range

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;

Super User
Super User
Posts: 7,039

Dropping observations by ID and variable range

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.

Super User
Posts: 10,020

Dropping observations by ID and variable range

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

Respected Advisor
Posts: 3,156

Dropping observations by ID and variable range

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

PROC Star
Posts: 7,468

Dropping observations by ID and variable range

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.

Respected Advisor
Posts: 3,156

Dropping observations by ID and variable range

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

Ask a Question
Discussion stats
  • 8 replies
  • 193 views
  • 0 likes
  • 5 in conversation