Deleting blank observations

[Granny]

How to delete blank observations in data table without deleting entire row? I got the data from an excel file which has a different number of observations in each column. So, how do I delete the blank observations without deleting the data in the same row but different column?

For a visual..

A B C

48 24 23
64 50 27
53 18 28
53 48 59
38 18
49

If A= ‘ ‘ Then Delete;

is deleting the last two observation for B and the last observation for C

I need to just delete the blank observations

[Granny]

Edit: I meant if I do

If C=‘ ‘ then Delete;

The last 2 observations for A and last observation for B are getting deleted

[PaigeMiller]

---

@Granny wrote:
How to delete blank observations in data table without deleting entire row? I got the data from an excel file which has a different number of observations in each column. So, how do I delete the blank observations without deleting the data in the same row but different column?

For a visual..

A B C

48 24 23
64 50 27
53 18 28
53 48 59
38 18
49

If A= ‘ ‘ Then Delete;

is deleting the last two observation for B and the last observation for C

I need to just delete the blank observations

---

In your example, you cannot delete the blank value of C unless you want to delete the entire row. That's how SAS works.

[FreelanceReinh]

Hi @Granny,

A "long" data structure might be more suitable for your data:

data have;
input A B C;
cards;
48 24 23
64 50 27
53 18 28
53 48 59
38 18 .
49 .  .
;

proc transpose data=have out=trans;
run;

proc transpose data=trans
               out=want(rename=(_name_=col col1=value) where=(value>.));
by _name_;
run;

proc print data=want;
run;

Then you have 6 observations for A, 5 for B and 4 for C, as desired, no missing values and a data structure that is useful for many purposes.

---

@Granny wrote:

If A= ‘ ‘ Then Delete;

---

You know that a blank (' ') represents a missing character value, whereas standard missing numeric values are represented by a period (.)?

[Tom]

Did you really mean to keep missing value between .A and .Z by using VALUE > . in where clause instead of NOT MISSING(VALUE) or VALUE > .Z?

[FreelanceReinh]

---

@Tom wrote:

Did you really mean to keep missing value between .A and .Z by using VALUE > . in where clause instead of NOT MISSING(VALUE) or VALUE > .Z?

---

Thanks, but don't worry, @Tom. 🙂 In my professional programs I would of course use one of these bulletproof criteria and I had considered it here as well. But in a response to a "New User" who appears to be struggling with the very basics of SAS I decided to keep the code as simple as possible.

Also, it is not even clear that the OP would really want to delete observations containing a special missing value (which might constitute a valuable piece of information). My understanding was that the missing data in question just indicated the end of a series of values. After all, according to the initial post the data came "from an excel file which has a different number of observations in each column."

[ballardw]

---

@Granny wrote:
How to delete blank observations in data table without deleting entire row? I got the data from an excel file which has a different number of observations in each column. So, how do I delete the blank observations without deleting the data in the same row but different column?

For a visual..

A B C

48 24 23
64 50 27
53 18 28
53 48 59
38 18
49

If A= ‘ ‘ Then Delete;

is deleting the last two observation for B and the last observation for C

I need to just delete the blank observations

---

Please be aware that the message windows on this forum will seriously reformat text with "blanks". To maintain any appearance with blanks then you should open a code box using the forum's {I} icon and paste there. From your text I have to assume that your actually have some data that looks more like:

A  B  C
48 24 23
64 50 27
53 18 28
53 48 59
   38 18
      49