A quick question about your last posting... Wouldn’t using a LOAD option on the SASFILE statement eliminate the need for the dummy step? [pre] sasfile work.test.data load; [/pre] ... View more

Hi data _null_, I don’t get it. Don’t all three programs rely on array references? Please help me to understand. Overall, it seems to me that Chang’s program is probably the best answer. His is the simplest program, it is comparable to the macro program that I posted in terms of efficiency (for my test dataset anyway), and I can see how it’s less problematic than using macros. I created the macro only because I was curious to see how using a single statement would compare to the other two excellent suggestions in terms of efficiency. Since I personally like Chang’s solution better than my own, I almost did not post my solution. But, then again, I suspected that if I did post the macro this thread could turn into a good learning experience for myself 🙂 Thanks again. ... View more

I was also curious to see how the hash solution compares for larger datasets with more variables. I ran a test on a dataset with 1000000 observations and 20 variables. Also, I tried testing a third solution which uses a simple macro to create a single statement in the data step. Here are the results. [pre] 1 data test; 2 array var{20}; 3 do i = 1 to 1000000; 4 do j=1 to dim(var); 5 var{j} = floor(ranuni(0)*300); 6 end; 7 output; 8 end; 9 drop i j; 10 run; NOTE: The data set WORK.TEST has 1000000 observations and 20 variables. NOTE: DATA statement used (Total process time): real time 3.57 seconds cpu time 3.57 seconds 11 12 data chang; 13 set test; 14 array var(*) var:; NOTE: The array var has the same name as a SAS-supplied or user-defined function. Parentheses following this name are treated as array references and not function references. 15 unique = 1; 16 drop p q; 17 do p = 1 to dim(var)-1 while(unique); 18 do q = p + 1 to dim(var) while(unique); 19 unique = var(p)^=var(q); 20 end; 21 end; 22 run; NOTE: There were 1000000 observations read from the data set WORK.TEST. NOTE: The data set WORK.CHANG has 1000000 observations and 21 variables. NOTE: DATA statement used (Total process time): real time 24.56 seconds cpu time 25.00 seconds 23 24 data data_null_; 25 length y 8 d $1; 26 if _n_ = 1 then do; 27 call missing(y,d); 28 declare hash h(); 29 h.definekey('y'); 30 h.definedata('d'); 31 h.definedone(); 32 end; 33 set test; 34 h.clear(); 35 array v var:; 36 do l=1 to dim(v) until(h.add(key:v ,data:' ') gt 0); 37 end; 38 unique = l eq dim(v)+1; 39 output; 40 drop y d l; 41 run; NOTE: There were 1000000 observations read from the data set WORK.TEST. NOTE: The data set WORK.DATA_NULL_ has 1000000 observations and 21 variables. NOTE: DATA statement used (Total process time): real time 36.87 seconds cpu time 37.23 seconds 42 43 %macro ne(array, elements); 44 %do i = 1 %to %eval(&elements-2); 45 %do j = %eval(&i+1) %to &elements; 46 (&array[&i] ne &array[&j]) & 47 %end; 48 %end; 49 (&array[%eval(&elements-1)] ne &array[&elements]) 50 %mend; 51 52 data _null_; 53 if 0 then set test; 54 array var var:; 55 call symputx('num_vars', dim(var)); 56 run; NOTE: DATA STEP stopped due to looping. NOTE: DATA statement used (Total process time): real time 0.01 seconds cpu time 0.01 seconds 57 58 data polingjw; 59 set test; 60 array var var:; 61 unique = (%ne(var, &num_vars)); 62 run; NOTE: There were 1000000 observations read from the data set WORK.TEST. NOTE: The data set WORK.POLINGJW has 1000000 observations and 21 variables. NOTE: DATA statement used (Total process time): real time 22.06 seconds cpu time 22.15 seconds [/pre] ... View more

I'm going to try pasting the code again. I think that my original posting was changed because it had an "i" enclosed in brackets. [pre] data old; input Product $ Open_date:mmddyy10. Amount_0111 Amount_0211 Amount_0311 Create_0111 Create_0211 Create_0311; format open_date mmddyy10.; datalines; blah 01/01/2010 100 150 180 1000 1000 1000 blah2 02/01/2010 100 150 180 1000 1000 1000 ; run; data new; set old; array amounts{*} amount:; array creates{*} create:; do k=1 to dim(amounts); amount = amounts{k}; month = scan(vname(amounts{k}), 2, '_'); do j=1 to dim(creates); if month=scan(vname(creates ), 2, '_') then do; create=creates{j}; leave; end; end; output; end; keep product open_date month amount create; run; proc print data=new noobs; var product open_date month amount create; run; [/pre] ... View more

Message was edited by: polingjw Message was edited by: polingjw ... View more

Your right. The documentation for the NEXTRVAL=n option on proc univariate states that it "specifies the number of extreme values that PROC UNIVARIATE lists in the table of extreme values. The table lists the n lowest unique values and the n highest unique values. By default, n=0 and no table is displayed." I use the univariate procedure rather infrequently. Thanks for pointing out my mistake. ... View more

In my actual program I only want to include those variables with the prefix “gen” in the array but there are a number of additional variables listed in the by statement. The array statement I’m going to use is [pre] array firstgen{*} ‘first.gen’n:; [/pre] [pre] [/pre] Therefore, I actually do need the validvarname=any option for my program to work. Thanks again for this great solution! ... View more

data _null_, Wow! I thought that the validvarname option was only for compatibility with DBMS variable names. This is exactly what I was hoping to find! ... View more

Thanks for posting this solution. I appreciate the help. ... View more

Thanks Scott. I had thought about writing a macro to accomplish this task, but I was hoping that there would be an easier alternative. I appreciate your advice. ... View more

I’m not sure if this is ultimately what you want, but it works for the simplified data that you posted. [pre] %let rows_to_sum = 3; data test; input var; datalines; 1 2 3 4 5 6 ; run; data _null_; call symputx("numvars", n); if 0 then set test nobs=n; run; data result; array vars{&numvars} _temporary_; do i=1 to dim(vars); set test; vars{i}=var; end; do i=1 to &numvars - &rows_to_sum + 1; result = 0; do j=i to i+&rows_to_sum-1; result+vars{j}; end; output; end; keep result; run; proc print data=result noobs; run; [/pre] ... View more

Take a look at this paper. It has some useful information about using duplicate keys to make SQL like joins with hash objects. support.sas.com/rnd/base/datastep/dot/better-hashing-sas92.pdf ... View more

On my computer, Chang’s solution took 32.54 seconds, real time. The univariate procedure took 1:59.84, almost four times as long. However, the results are not equivalent. The original posting asked for the top five and bottom five distinct values. The univariate procedure does not produce distinct results. ... View more

Chang, I’m impressed by your much more efficient program. In a dataset with one million observations, your program runs in about 1/4th the time that mine does. I actually thought about using a multi-dimensional array solution when I first read the original posting but quickly gave up once I started getting confused by my own program. Thanks for demonstrating the better path forward! ... View more