Use temporary array/do loop to check the value of one variable/one observation against many values

[clambert22]

Hi all! Hoping for some help or new ideas ....I am trying to test whether the value of a variable for one observation is greater than the value of another variable for any observation in my table. See code and log below:

/*First, I add this list of values from Column1 into a new macrovariable varlist, separated by a space.*/

proc sql;
%let varlist = ;
select Column1 into :varlist separated by ' '
from have;
quit;

/*Then, I add the count of all the values added into the previous macro into a new macrovariable a. I could just check the log/length of my table but I'd like this to be reusable for other datasets and I'm too lazy to look every time.*/

proc sql;
%let a = ;
select count(Column1) into :a
from have;
quit;

/*Then, I add the macro variables from above into a SAS temporary array and attempt to use a do loop to look for, and flag, rows where the value of Column 2 is greater than the value of Column 1. I want to know if Column 2 is greater than Column 1 anywhere in the table, not just for the observation it's listed next to.*/

data want;
set have;
array variables (&a.) &varlist.;

do i = 1 to &a.;

if variables(i) > Column2 then flag = 1;

end;

run;

Here's my log output when I attempt to run this:

314 proc sql;
315 %let varlist = ;
316 select Column1 into :varlist separated by ' '
317 from have;
318 quit;
NOTE: PROCEDURE SQL used (Total process time):
real time 0.04 seconds
cpu time 0.01 seconds

319
320 proc sql;
321 %let a = ;
322 select count(Column1) into :a
323 from have;
324 quit;
NOTE: PROCEDURE SQL used (Total process time):
real time 0.02 seconds
cpu time 0.01 seconds

325
326 data want;
327 set have;
328 array variables (&a.) &varlist.;
-
79
ERROR 79-322: Expecting a ).

NOTE: Line generated by the macro variable "VARLIST".
1 3679 4455 4179 3747 2113 4168 4183 3157 3240 4165 5252 3981 3788 2079 3079 2867 3840 4638
---- ----
352 79
1 ! 1719 2912 5558 4679 4328 4134 2587 2709 2258 3923 4377 2873 2290 5172 4699 2541 3466 3496
1 ! 3966 3041 3728 4693 4649 2274 3012 4551 3955 4958 3021 3592 1479 3684 4698
ERROR 352-185: The length of numeric variables is 3-8.

ERROR 79-322: Expecting a (.

329
330 do i = 1 to 77;
WARNING: Partial value initialization of the array variables.
331
332 if variables(i) > Column2 then flag = 1;
333
334 end;
335
336 run;

NOTE: The SAS System stopped processing this step because of errors.
WARNING: The data set WORK.TEST may be incomplete. When this step was stopped there were 0
observations and 90 variables.
WARNING: Data set WORK.TEST was not replaced because this step was stopped.
NOTE: DATA statement used (Total process time):
real time 0.04 seconds
cpu time 0.01 seconds

______

I feel like this is pretty inefficient and also...it's not working! Thanks in advance for any insight you can provide!

[Astounding]

Yes, the programming has gotten a little out of hand here.  Try it this way:

proc sql;
   select max(column2) into : maxval from have;
quit;

data want;
   set have;
   flag = (column1 > &maxval);
run;

I'm not sure if I got the interpretation correct, or whether you might have to switch around the roles of column1 and column2 here  But the final program looks like it should be this simple.

********* EDITED:

Maybe this is closer to what you want?

proc sql;
   select max(column1) into : maxval from have;
run;

data want;
   flag=0;
   do until (done or flag=1);
      set have;
      if column2 > &maxval then flag=1;
   end;
   output;
   stop;
run;

Probably, the right answer is neither of these but uses some combination of these techniques.  I would guess you can work it out one way or the other. 

[Tom · Accepted solution]

The right answer depends on what you mean by this phrase:

I am trying to test whether the value of a variable for one observation is greater than the value of another variable for any observation in my table. 

Let's call the two variables X and Y.  Do you want to find out if the value of X exceeds the MIN or the MAX value of Y?  Are you testing if X is larger than ANY (non-missing) value of Y? Or X is larger than ALL  values of Y?

Here is code to make both flags.

proc sql;
create table want as
  select a.*
      , (a.X > max(a.Y)) as FLAG1 label="X larger than all Y"
      , (a.X > min(a.Y)) as FLAG2 label="X larger than any Y"
  from have a
;
quit;

If you did want to try your approach you need to realize you are pulling the VALUES of COLUMN1 into the macro variable and not the NAMES  of variables and adjust your ARRAY statement appropriately.

%let value_list=.;
proc sql noprint;
select distinct Column1 into :value_list separated by ' '
from have;
%let a=&sqlobs;
quit;

....
array values (&a.) _temporary_ (&value_list);
....