topic Re: Deleting blank observations in SAS Studio

Deleting blank observations

Granny — Sun, 24 Nov 2019 00:51:06 GMT

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

Re: Deleting blank observations

Granny — Sun, 24 Nov 2019 00:52:32 GMT

Edit: I meant if I do

If C=‘ ‘ then Delete;

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

Re: Deleting blank observations

PaigeMiller — Sun, 24 Nov 2019 00:56:19 GMT

@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.

Re: Deleting blank observations

FreelanceReinh — Sun, 24 Nov 2019 11:39:14 GMT

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 (.)?

Re: Deleting blank observations

Tom — Sun, 24 Nov 2019 15:28:06 GMT

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?

Re: Deleting blank observations

FreelanceReinh — Sun, 24 Nov 2019 16:56:41 GMT

@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."

Re: Deleting blank observations

ballardw — Tue, 26 Nov 2019 00:14:03 GMT

@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: