Check for special values in numbered range lists

[Georg_UPB]

Hi everyone,

I've used numbered range lists to input variables and to check them for missing values, which worked quite well: If nmiss(of a1-a3 b1-b3)>0 Then...

Now, I need to also check if any of the variables is zero. Therefore, I enhanced the If-clause accordingly: If nmiss(of a1-a3 b1-b3)>0 or min(of a1-a3 b1-b3)=0 Then...

Interestingly, this slows down processing time considerably (on my machine for >60 variables). Although I can live with that, I wonder if there's a better approach.

Data zzz;

    Input a1-a3 b1-b3;

    If nmiss(of a1-a3 b1-b3)=0 And min(of a1-a3 b1-b3)>0 Then Output;

Datalines;

1 2 3 4 5 6

1 . 3 4 5 6

1 2 3 4 0 6

;

Run;

[RW9]

You could consider normalizing your data, so something like:

Section  Type  Value

1               A1        1

1               A2        2

...

3               B3         6

Then you could use SQL to sum and find missing values.  This may help with performance, however the secondary benefit of this is scalability.  So you have >60 variables at the moment, this is 60 rows per group, however what about 120, or more.  As for your example, I couldn't see any way of speeding it up other than that.

[LinusH]

If your source data is in SAS tables, and if you want to subset your data with this condition, use WHERE instead,

Otherwise, I can't see any real alternative. Syntactically you could use an array instead, but it's very unlikely that it could be more efficient than built-in functions.

[Haikuo · Accepted solution]

Not sure if it makes any difference, but you may try to replace two functions with only one "ORDINAL",

Data zzz;

    Input a1-a3 b1-b3;

/*    If nmiss(of a1-a3 b1-b3)=0 And min(of a1-a3 b1-b3)>0;*/

      if ordinal(1,of a1-a3 b1-b3) >0;

Datalines;

1 2 3 4 5 6

1 . 3 4 5 6

1 2 3 4 0 6

;

Run;

Haikuo

[data_null__]

Maybe it will be a bit faster if you split the expressions into two subsetting IFs.  This way MIN is only executed for observations that have no missing values.

Data zzz2;
   Input a1-a3 b1-b3;
   If nmiss(of a1-a3 b1-b3) eq 0;
   if min(of a1-a3 b1-b3) gt 0;
   Datalines;
1 2 3 4 5 6
1 . 3 4 5 6
1 2 3 4 0 6
;;;;
   Run;