Removing Specific Rows in a Dataset

Accepted Solution Solved
Reply
Regular Contributor
Posts: 183
Accepted Solution

Removing Specific Rows in a Dataset

Hi Experts,

The following is my sample dataset. First column contains the Date and other columns are firms. I have just included only four firms here; I have many more firms in my original dataset. If you see, observations are zeros for almost all firms in specific dates (for example, 01/01/2004, 19/01/2004 etc.). I need to remove those rows from my dataset. How can I do that?


By the by, why can't I upload the SAS datafile here?

Abu



Date          CH00186667  US00208J10  US01167P10  BMG89998210

01/01/20040000
02/01/2004-0.0143337540.0208526910.005923507-0.026891625
05/01/2004-0.06504876-0.027125565-0.063793667-0.046109507
06/01/20040.1344999020.0126171970.009120535-0.048214012
07/01/20040.041180928-0.008999022-0.02407944-0.02589243
08/01/20040.083399573-0.032067022-0.025253911-0.094661522
09/01/20040.0233327550.015155970.0026829080.090551284
12/01/2004-0.010596278-0.004479334-0.046272924-0.016743585
13/01/20040.081842205-0.047210248-0.023050375-0.032730347
14/01/20040.240379787-0.028102609-0.0190630440.028377054
15/01/20040.055129156-0.072989994-0.037508157-0.113781919
16/01/2004-0.1072189910.0100760650.0173954670.013286573
19/01/20040000
20/01/2004-0.102122217-0.033188884-0.024621075-0.064291383
21/01/20040.006716838-0.00313470.011238732-0.106147952
22/01/20040.022895808-0.036231344-0.0010347440.15486874
23/01/20040.0011215890.1044808250.0239191550.002775815
26/01/2004-0.035955148-0.003662896-0.0647077520.008888869
27/01/20040.0479436020.0295896690.0407302370.047567453
28/01/20040.024591751-0.0328918280.015808341-0.036130603
29/01/2004-0.020291339-0.08556701-0.0072508130.006551547
30/01/2004-0.0215688420.013072352-0.0141423460.042799423
02/02/2004-0.093161236-0.037045030.004789916-0.000658193
03/02/2004-0.036070915-0.0105479380.0179376740.040776061
04/02/2004-0.002778098-0.0199171820.0243028750.001851073
05/02/20040.269316448-0.057050738-0.00705107-0.077396759
06/02/2004-0.1537267510.035610327-0.0351626060.002378226
09/02/20040.1440707040.027980127-0.0091660810.034578842
10/02/2004-0.034547910.110229835-0.00011394-0.084497257
11/02/2004-0.031655654-0.024520682-0.0320017390.046010137
12/02/2004-0.035674229-0.041773807-0.0172399470.048189697
13/02/2004-0.0350261920.0183323050.001640581-0.008376772
16/02/2004000-0.019097575
17/02/20040.001044875-0.039156187-0.022763521-0.046791297
18/02/20040.0446193670.020950882-0.0345971690.034137212
19/02/20040.0046839940.009618490.0341298350.081376146
20/02/20040.0128872590.0089570720.014299227-0.0762263



Accepted Solutions
Solution
‎03-18-2015 12:08 PM
PROC Star
Posts: 7,471

Re: Removing Specific Rows in a Dataset

Posted in reply to AbuChowdhury

So why don't you just use something like:

data want;

  set have;

  if (min(of CH00186667--BMG89998210) = max(of CH00186667--BMG89998210)=0)

   or date eq '16FEB2004'd

   then delete;

run;

View solution in original post


All Replies
Super User
Super User
Posts: 7,042

Re: Removing Specific Rows in a Dataset

Posted in reply to AbuChowdhury

Do you want to delete the observations where all firms have a value of 0?

if min(of CH00186667  US00208J10  US01167P10  BMG89998210) = max(of CH00186667  US00208J10  US01167P10  BMG89998210)=0 then delete;

PROC Star
Posts: 7,471

Re: Removing Specific Rows in a Dataset

Posted in reply to AbuChowdhury

Same suggestion as Tom, but you can use variable lists.  e.g.:

  set have;

  if min(of CH00186667--BMG89998210) = max(of CH00186667--BMG89998210)=0

   then delete;

run;

Regular Contributor
Posts: 183

Re: Removing Specific Rows in a Dataset

The code works but two concerns again. First, in some Dates I see that the observations are zeros not for all firms; some firms have other values. I also need to remove those. Second, if I want to remove just one or two Dates separately, what can I do?

Super User
Super User
Posts: 7,954

Re: Removing Specific Rows in a Dataset

Posted in reply to AbuChowdhury

You may be better or re-shaping your data then.  E.g.:

DATE     COMPANY     RESULT

01/01/04  CH00186667   0

01/01/04  US00208J10   0

...

You can then runs sums and such like to evaluate if all in date are 0, or specific checks across companies etc.

Regular Contributor
Posts: 183

Re: Removing Specific Rows in a Dataset

If you look at the 16/02/2004, the values are zeros for three firms. Your codes do not delete this row; I also need to remove this row. How can I do that?

Again, say I also want to delete 11/02/2004 although in this Date all firms have values? How can I do that?

Date          CH00186667  US00208J10  US01167P10  BMG89998210

11/02/2004-0.031655654-0.024520682-0.0320017390.046010137
12/02/2004-0.035674229-0.041773807-0.0172399470.048189697
13/02/2004-0.0350261920.0183323050.001640581-0.008376772
16/02/2004000-0.019097575
17/02/20040.001044875-0.039156187-0.022763521-0.046791297
Super User
Super User
Posts: 7,042

Re: Removing Specific Rows in a Dataset

Posted in reply to AbuChowdhury

What code did you try?  What happened?

Regular Contributor
Posts: 183

Re: Removing Specific Rows in a Dataset

I have tried the code made by Arthur Tabachneck. He modified your code. If I use that code, then those dates are deleted where all firms have 0 values. But for example, all firms do not have 0 values in 16/02/2004. By using Arthur's code, this 16/02/2004 is not deleting; but I want to delete this date as well. Again, although no firm has 0 values in 11/02/2004, I would like to delete this date as well.

Date          CH00186667  US00208J10  US01167P10  BMG89998210

11/02/2004-0.031655654-0.024520682-0.0320017390.046010137
12/02/2004-0.035674229-0.041773807-0.0172399470.048189697
13/02/2004-0.0350261920.0183323050.001640581-0.008376772
16/02/2004000-0.019097575
17/02/20040.001044875-0.039156187-0.022763521-0.046791297
Super User
Super User
Posts: 7,042

Re: Removing Specific Rows in a Dataset

Posted in reply to AbuChowdhury

So how did you try to modify it to meet your new conditions?

Try re-stating the problem and see what SAS syntax for such a statement would be.  Then run it and check your results. 

Let me get you started. 


English:  Delete rows where DATE is in my list of specific dates.

How would you translate that into a SAS statement?

English: Delete rows where at least one of the company variables has a value of zero.

How would you translate that into a SAS statement?

Super User
Posts: 5,504

Re: Removing Specific Rows in a Dataset

Posted in reply to AbuChowdhury

The sample code works as planned, but the plan is to remove rows with all zeros.  If you want to delete rows with "almost all" zeros, you will have to tell us what that means.

Regular Contributor
Posts: 183

Re: Removing Specific Rows in a Dataset

Posted in reply to Astounding

That means, I would like to delete those dates when the values of all firms are 0 and when values of some firms are 0. I would also like to delete any date (by my choice)  as I explained in my reply to Tom.

Solution
‎03-18-2015 12:08 PM
PROC Star
Posts: 7,471

Re: Removing Specific Rows in a Dataset

Posted in reply to AbuChowdhury

So why don't you just use something like:

data want;

  set have;

  if (min(of CH00186667--BMG89998210) = max(of CH00186667--BMG89998210)=0)

   or date eq '16FEB2004'd

   then delete;

run;

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 11 replies
  • 706 views
  • 1 like
  • 5 in conversation