Help using Base SAS procedures

how do i delete rows 5605 to 6302 and deleting NAs.

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 102
Accepted Solution

how do i delete rows 5605 to 6302 and deleting NAs.

thank you so so much in advance.

data r4; set r3; delete (5605:6302); run;

is not working.

i also want to delete NAs. but when i say if cusip="na"

or if cusip=na

or if cusip='na'.....none of them works...


Accepted Solutions
Solution
‎07-11-2017 03:24 PM
SAS Super FREQ
Posts: 8,868

Re: how do i delete rows 5605 to 6302 and deleting NAs.

Hi:

  I would recommend that you verify the values of the CUSIP variable, something like:

    

proc freq data=r3;

  tables cusip;

run;
     

  For example, you might find that the values are NA (all caps) or N/A or n/a or Not Applicable-- and you need to know EXACTLY how the variable contains the values and the correct case, because comparisons of text values are case sensitive and so, you may have to do something like this:

if upcase(cusip) = 'NA' then delete;

or

if upcase(cusip) in ('N/A', 'NA', 'NOT APPLICABLE') then delete;
     

  As for your other question, your DELETE syntax is completely wrong. The automatic variable that sometimes is used to represent the row number is _N_ -- although this is not a fixed column in the data and really represents the number of times the DATA step program has iterated. But a statement like this could work:

if _n_ ge 5605 and _n_ le 6302 then delete;

But I generally recommend that you verify that the observations you want to delete will be correctly deleted using _N_, it might be safer to find another variable value that would identify the observations you want to delete. For example, if the data are sorted before your program, the value of _N_ might no longer identify the rows you want to delete.

cynthia

View solution in original post


All Replies
Contributor
Posts: 58

Re: how do i delete rows 5605 to 6302 and deleting NAs.

Character variables are case sensitive in SAS. Try changing to

if cusip='NA' then delete;

Solution
‎07-11-2017 03:24 PM
SAS Super FREQ
Posts: 8,868

Re: how do i delete rows 5605 to 6302 and deleting NAs.

Hi:

  I would recommend that you verify the values of the CUSIP variable, something like:

    

proc freq data=r3;

  tables cusip;

run;
     

  For example, you might find that the values are NA (all caps) or N/A or n/a or Not Applicable-- and you need to know EXACTLY how the variable contains the values and the correct case, because comparisons of text values are case sensitive and so, you may have to do something like this:

if upcase(cusip) = 'NA' then delete;

or

if upcase(cusip) in ('N/A', 'NA', 'NOT APPLICABLE') then delete;
     

  As for your other question, your DELETE syntax is completely wrong. The automatic variable that sometimes is used to represent the row number is _N_ -- although this is not a fixed column in the data and really represents the number of times the DATA step program has iterated. But a statement like this could work:

if _n_ ge 5605 and _n_ le 6302 then delete;

But I generally recommend that you verify that the observations you want to delete will be correctly deleted using _N_, it might be safer to find another variable value that would identify the observations you want to delete. For example, if the data are sorted before your program, the value of _N_ might no longer identify the rows you want to delete.

cynthia

New Contributor
Posts: 3

Re: how do i delete rows 5605 to 6302 and deleting NAs.

Hi, Try this code. I have used Firestobs and obs options.

Also i have tried as Cynthia suggested, it is also working.

I have suggested another logic.

Kindly let me know your reviews.

   data a  ;

    input x y $;

    cards;

    1 a

    2 b

    3 NA

    4 d

    5 e

    6 f

    7 g

    8 h

    9 i

    10 j

    11 k

    12 NA

    13 NA

    14 a

    15 b

    16 NA

    17 d

    18 d

    19 i

    20 e

    21 f

    22 m

    23 x

    24 v

    25 NA

    ;

    

/* if I don't want to include observations between 8 and 14  and observations having "NA" values*/

    data a1;

     set a (obs=7);

     if y="NA" then delete;

    run;

   

    data a2;

     set a (firstobs=15 obs=max);

     if y="NA" then delete;

    run;

   

    data a_final;

     set a1 a2;

    run;

/* As per Cynthia's suggestion */ 

    data a_111;

     set a;

     if _N_ ge 8 and _N_ le 14 or y in ('NA' 'N/A' 'Not Applicable') then delete;

    run;

🔒 This topic is solved and locked.

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

Discussion stats
  • 3 replies
  • 6755 views
  • 0 likes
  • 4 in conversation