BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
AbuChowdhury
Fluorite | Level 6

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


1 ACCEPTED SOLUTION

Accepted Solutions
art297
Opal | Level 21

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

11 REPLIES 11
Tom
Super User Tom
Super User

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;

art297
Opal | Level 21

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;

AbuChowdhury
Fluorite | Level 6

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?

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

AbuChowdhury
Fluorite | Level 6

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
Tom
Super User Tom
Super User

What code did you try?  What happened?

AbuChowdhury
Fluorite | Level 6

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
Tom
Super User Tom
Super User

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?

Astounding
PROC Star

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.

AbuChowdhury
Fluorite | Level 6

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.

art297
Opal | Level 21

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;

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!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

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
  • 11 replies
  • 56034 views
  • 1 like
  • 5 in conversation