How to delete empty row observations for data with multiple rows per subject

[Dbynoe]

My data contains several rows of data per ID (each row pertains to a particular member of their network), and I'd like to remove rows with no data. In order words, I'd like code for the "Ideal SAS Output" (thank you in advance for your help):

                                                                                              Current SAS Output

Obs ID r1 r2 r3 r17 r18 r19 1 2 3 4 5 6 7 8 9 10 11 12 13 14

1234	2	34	1	2	2	77
1234	2	35	0	2	2	77
1234	2	30	0	2	2	77
1234	2	37	0	2	2	77
1234	.		.	.	.	.
1234	.		.	.	.	.
1234	.		.	.	.	.
1234	.		.	.	.	.
1234	.		.	.	.	.
1234	.		.	.	.	.
1234	.		.	.	.	.
1234	.		.	.	.	.
1234	2	4	.	.	.	.
1234	2	2	.	.	.	.

Ideal SAS Output

Obs ID r1 r2 r3 r17 r18 r19 1 2 3 4 5  

1234	2	34	1	2	2	77
1234	2	35	0	2	2	77
1234	2	30	0	2	2	77
1234	2	37	0	2	2	77
1234	2	4	.	.	.	.

[ballardw]

I can't tell what your variable names are but

data want;

    set have;

    if cmiss(id, r1,r2,r3 ...) = <the number of variables you put there> then delete;

run;

should do it. Put the actual list of variables  inside the () and make sure that the number compared is the number of variables in the list.

[Dbynoe]

Thank you so much for the response! I really do appreciate it. The dataset I'm working with has more than 4000 varialbes, do you perhaps know of a more time efficient ways of acheiving the same goal?

[Astounding]

It looks like ID is always present, even when you want to delete the observation.  Is ID character or numeric?

[Dbynoe]

IDs are numeric for this dataset!

[Astounding · Accepted solution]

Try it this way then:

if n(of _numeric_) <= 1 and cats(of _character_)=' ' then delete;

Technically, it might not be possible for the numerics to all be missing so the less than sign might not be needed.  But it caters to the possibility that ID might also be missing.

[rogerjdeangelis]

Can easily add character variables.

data have;
input ID r1 r2 r3 r17 r18 r19;
cards4;
1234 2 34 1 2 2 77
1234 2 35 0 2 2 77
1234 2 30 0 2 2 77
1234 2 37 0 2 2 77
1234 . . . . . .
1234 . . . . . .
1234 . . . . . .
1234 . . . . . .
1234 . . . . . .
1234 . . . . . .
1234 . . . . . .
1234 . . . . . .
1234 2 4 . . . .
1234 2 2 . . . .
;;;;
run;quit;


data want;
  set have;
  array nums[*] _numeric_;
  if cmiss(of nums[*]) ne (dim(nums)-1) then output;
run;quit;


Up to 40 obs WORK.WANT total obs=6

Obs ID R1 R2 R3 R17 R18 R19

 1 1234 2 34  1 2    2   77
 2 1234 2 35  0 2    2   77
 3 1234 2 30  0 2    2   77
 4 1234 2 37  0 2    2   77
 5 1234 2 4   . .    .   .
 6 1234 2 2   . .    .   .

[Dbynoe]

Thank you so much for your response. I copied your code and ran it, and it works just fine; however, it's not working for my dataset. Should the array num[*] _numeric_;  work if there are variables in the dataset that are both numeric and character? I tried using array num[*] _all_; but SAS wasn't too happy with that. 

[Dbynoe]

Because I have both numeric and character variables in the dataset I've tried this, but it was unsuccessful:

data want;
set have;
array nums[*] _numeric_;
array char[*] _character_;
if cmiss(of nums[*]) ne ((dim(nums) and dim(char))-1) then output;
if cmiss(of char[*] ne ((dim(nums) and dim(char))-1) then output;
run;quit;

Do you have any advice on how to correct the code? I've been working on this for about two weeks, and I really appreciate everyone's help! I'm hoping to finally finish the task today.

[Astounding]

I gave you a simple one-line solution last Friday.  Did you not try it?

[Dbynoe]

I tried it, and a few variations of it, but it didn't work. Perhaps I'm missing something on my end. 

[Astounding]

Didn't work?  Does that mean gave you an error, or returned the wrong observations, or something else?

I did just test it ... it's working for me.

[Dbynoe]

Sorry for not being clear. The output didn't change after running the code, and there are no "errors" on the log. The missing rows are stlil present.

[Astounding]

OK, post the exact statement you are adding.  There must be a small difference between that and what I posted.

[Dbynoe]

I copied and pasted your code into my program: 

if n(of _numeric_) <= 1 and cats(of _character_)=' ' then delete;

I also tried the following variations:

if n(of _numeric_) < 1 and cats(of _character_)=' ' then delete;

if nmiss(of _numeric_) ge 1 and cats(of _character_)=' ' then delete;

if nmiss(of _numeric_) > 1 and cats(of _character_)=' ' then delete;

if cmiss(of _numeric_) ge 1 and cats(of _character_)=' ' then delete;

if cmiss(of _numeric_) > 1 and cats(of _character_)=' ' then delete;