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
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
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 | . | . | . | . |
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.
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.
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?
It looks like ID is always present, even when you want to delete the observation. Is ID character or numeric?
IDs are numeric for this dataset!
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.
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 . . . .
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.
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.
I gave you a simple one-line solution last Friday. Did you not try it?
I tried it, and a few variations of it, but it didn't work. Perhaps I'm missing something on my end.
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.
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.
OK, post the exact statement you are adding. There must be a small difference between that and what I posted.
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;
Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.
If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website.
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.