topic Re: How to delete empty row observations for data with multiple rows per subject in SAS Procedures

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

Dbynoe — Fri, 03 Feb 2017 18:56:18 GMT

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

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

ballardw — Fri, 03 Feb 2017 20:45:30 GMT

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.

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

Dbynoe — Fri, 03 Feb 2017 20:54:52 GMT

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?

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

Astounding — Fri, 03 Feb 2017 21:03:40 GMT

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

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

Dbynoe — Fri, 03 Feb 2017 21:13:42 GMT

IDs are numeric for this dataset!

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

Astounding — Fri, 03 Feb 2017 21:20:13 GMT

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.

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

rogerjdeangelis — Fri, 03 Feb 2017 21:21:50 GMT

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

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

ballardw — Sun, 05 Feb 2017 03:36:18 GMT

@Dbynoe wrote:

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?

That many variables is often a symptom of a poor data structure or process design. If by any chance you have a process that is constantly adding new variables every week/month/ or other period then the process is flawed and should be reconsidered. It is much easier to work with data that has a variable to indicate processing period, date or source with the same variables and then use BY group processing. If you need a REPORT for people to read (4000 columns, really?) then report procedures such as Proc Report and Tabulate are very good at creating such things.

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

Dbynoe — Mon, 06 Feb 2017 18:41:21 GMT

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.

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

Dbynoe — Mon, 06 Feb 2017 19:14:47 GMT

I didn't design the dataset, I just received it from a data coordination center my boss contracted. I'm trying to create 4 subsets of data from the larger dataset that are more accessible.

This particular dataset is a Social Network Analysis, and provides information on many subjects and up to 48 alters for each subjects' network. For example, r1 is the question: "is this person male or female". There are 48 potential responses per subject. So the person who created the dataset provided one line of row per subject, so they created r1_1-r1_48 as variables that indicate the alter's sex, with each variable linked to a unique alter. There are a lot of questions, so that's one reason why there are so many variables in this dataset. I rearranged the dataset so that r1 now represents all the data from r1_1-r1_48...hence the repeated ID measures. It is a very messy dataset, so it's definitely forced me to learn a lot more about SAS than I expected!

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

Dbynoe — Tue, 07 Feb 2017 15:49:41 GMT

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.

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

Astounding — Tue, 07 Feb 2017 16:36:38 GMT

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

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

Dbynoe — Tue, 07 Feb 2017 16:39:55 GMT

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

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

Astounding — Tue, 07 Feb 2017 16:51:27 GMT

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.

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

Dbynoe — Tue, 07 Feb 2017 16:50:17 GMT

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.

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

Astounding — Tue, 07 Feb 2017 16:52:17 GMT

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

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

Dbynoe — Tue, 07 Feb 2017 16:57:11 GMT

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;

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

Astounding — Tue, 07 Feb 2017 17:02:14 GMT

Ah, now the plot thickens. The code looks fine, so the number one suspect is the data. What looks like blanks for character values may not actually be blanks. Take a few variables that appear to contain blanks, and print them in hex form. For example, if a variable has a length of $ 5, print it (for just one observation):

put varname varname $hex10.;

Dollars to doughnuts there will be some strange characters in there (hex nulls, carriage returns .... we'll find out).