BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Dbynoe
Fluorite | Level 6

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

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Astounding
PROC Star

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

17 REPLIES 17
ballardw
Super User

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
Fluorite | Level 6

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
PROC Star

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

Dbynoe
Fluorite | Level 6

IDs are numeric for this dataset!

 

Astounding
PROC Star

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
Barite | Level 11
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
Fluorite | Level 6

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
Fluorite | Level 6

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
PROC Star

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

Dbynoe
Fluorite | Level 6

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

 

 

Astounding
PROC Star

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
Fluorite | Level 6

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
PROC Star

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

Dbynoe
Fluorite | Level 6

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;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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