BookmarkSubscribeRSS Feed
randyl
Calcite | Level 5

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
8 REPLIES 8
art297
Opal | Level 21

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?

randyl
Calcite | Level 5

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

art297
Opal | Level 21

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;

Tom
Super User Tom
Super User

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.

Ksharp
Super User

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

Haikuo
Onyx | Level 15

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

art297
Opal | Level 21

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.

Haikuo
Onyx | Level 15

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

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
  • 8 replies
  • 804 views
  • 0 likes
  • 5 in conversation