Help using Base SAS procedures

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

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 14
Accepted Solution

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

[ Edited ]

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
123423412277
123423502277
123423002277
123423702277
1234. ....
1234. ....
1234. ....
1234. ....
1234. ....
1234. ....
1234. ....
1234. ....
123424....
123422....

 

Ideal SAS Output

Obs ID r1 r2 r3 r17 r18 r19 1 2 3 4 5  
123423412277
123423502277
123423002277
123423702277
12342....
       

 

 


Accepted Solutions
Solution
‎02-08-2017 04:57 PM
Super User
Posts: 5,516

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

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.

View solution in original post


All Replies
Super User
Posts: 11,343

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

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.

Occasional Contributor
Posts: 14

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

[ Edited ]

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?

Super User
Posts: 5,516

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

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

Occasional Contributor
Posts: 14

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

Posted in reply to Astounding

IDs are numeric for this dataset!

 

Solution
‎02-08-2017 04:57 PM
Super User
Posts: 5,516

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

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.

Valued Guide
Posts: 505

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

Posted in reply to Astounding
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   . .    .   .

Occasional Contributor
Posts: 14

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

Posted in reply to rogerjdeangelis

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. 

Occasional Contributor
Posts: 14

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

Posted in reply to rogerjdeangelis

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.

Super User
Posts: 5,516

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

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

Occasional Contributor
Posts: 14

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

Posted in reply to Astounding

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

 

 

Super User
Posts: 5,516

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

[ Edited ]

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.

Occasional Contributor
Posts: 14

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

Posted in reply to Astounding

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.

Super User
Posts: 5,516

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

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

Occasional Contributor
Posts: 14

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

Posted in reply to Astounding

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;

☑ This topic is solved.

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

Discussion stats
  • 17 replies
  • 364 views
  • 4 likes
  • 4 in conversation