Thanks Rob.  Like I said, I didn't look back at my code to optimize (doh!).  Here is a update with your suggested improvement. ORIGINAL: data want; set have; array col[&vmax]; do i=1 to fact(dim(col));   call allperm(i, of col );   output; end; keep col1-col3; run; proc sort data=want nodupkey; by _all_; run; data want; set want; call sortn(of col1-col3); run; proc sort data=want nodupkey; by _all_; run; REPLACEMENT: data want; set have; array col[&vmax]; do i=1 to comb(&vmax,3);   call allcomb(i, 3, of col );   output; end; keep col1-col3; run; ... View more

Here is my solution using base sas, I through it together pretty sloppily and did not account for the 6th clue yet at the time I wrote this.  I am going to work on a more elegant solution. data _ps; array sd[5]; do i=100 to 236;   ps=i**2;   do d=1 to dim(sd);    sd =substrn(ps,d,1);   end;   if max(of sd1-sd5)<=5 and min(of sd1-sd5)>0 then output; end; keep ps; run; proc transpose data=_ps out=have(drop=_:); run; proc contents data=have out=_contents noprint; run; proc sql noprint; select max(varnum) into :vmax    from _contents; quit; proc datasets library=work nolist; delete _:; quit; data want; set have; array col[&vmax]; do i=1 to fact(dim(col));   call allperm(i, of col );   output; end; keep col1-col3; run; proc sort data=want nodupkey; by _all_; run; data want; set want; call sortn(of col1-col3); run; proc sort data=want nodupkey; by _all_; run; data want; /* update - I add this for step 6 */ retain yes '1'; set want; array _a[5]; array _b[5]; array _c[5]; do _i=1 to 5;   _a[_i]=substrn(col1,_i,1); _b[_i]=substrn(col2,_i,1); _c[_i]=substrn(col3,_i,1); end; _nbrs=cats(of _a _b _c ); count1=count(_nbrs,'1'); count2=count(_nbrs,'2'); count3=count(_nbrs,'3'); count4=count(_nbrs,'4'); count5=count(_nbrs,'5'); if count1 in (2,3,4,5) and count2 in (1,3,4,5) and count3 in (1,2,4,5) and count4 in (1,2,3,5) and count5 in (1,2,3,4); _d1=count1; _d2=count2; _d3=count3; _d4=count4; _d5=count5; call sortn(of _d1-_d5); if cats(of _d1-_d5) eq '12345'; drop _:; run; /* update - I add the following to enforce rule 6 */ data _null_; set want; by yes; array count[5]; if first.yes then   do;    array _f[5];   end; do _i=1 to 5;   if count[_i]=1 then _f[_i]+1; end; retain key; if last.yes then   do;    do _i=1 to 5;     if _f[_i]=1 then key=_i;    end;    call symput('key',strip(key));   end; run; data answer; set want; where count&key=1; keep col:; run; proc print noobs; run; ... View more

Thanks Rob, and I believe that your analysis of the last statement is accurate. ... View more

The SPDS library would always need to exist in order for a view to access it.  You could add the libname assignment as a auto executed piece of code on session startup if you have access to the end-user config files. ... View more

The last part of the puzzle you mention I take to as more of a clue to those attempting to solve the problem by hand, rather than an actual rule.  I am able to calculate 7 unique solutions that fit all criteria.  I am not sure I follow the issue you have with the other 6 solutions your program finds.  Could you post the full output as I do not license the operations research product from SAS necessary to run this unfortuneatly. To save people time from downloading the attachment here is Rob's solution. RobPratt wrote: data square_data;   retain square digitcount:;   keep square digitcount:;   array digitcount[0:9] digitcount0-digitcount9;   do n = floor(sqrt(1e4)) to sqrt(1e5-1);   square = n**2;   do d = 0 to 9;   digitcount = count(put(square,5.),put(d,1.));   end;   output;   end; run; proc optmodel;   set SQUARES;   set DIGITS = 0..9;   num digitcount {SQUARES, DIGITS};   read data square_data into SQUARES=[square] {d in DIGITS} <digitcount[square,d]=col('digitcount'||d)>;   /* Select = 1 if square s is selected, 0 otherwise */   var Select {SQUARES} binary;   /* IsDigitUsedCountTimes[d,c] = 1 if digit d is used c times, 0 otherwise */   set COUNTS = DIGITS;   var IsDigitUsedCountTimes {DIGITS,COUNTS} binary;   for {d in DIGITS diff {0}} fix IsDigitUsedCountTimes[d,d] = 0;   /* UseDigit = 1 if digit d is used, 0 otherwise */   impvar UseDigit {d in DIGITS} = 1 - IsDigitUsedCountTimes[d,0];   /* select exactly three squares */   con Select_three:   sum {s in SQUARES} Select = 3;   /* use exactly five digits */   con Use_five:   sum {d in DIGITS} UseDigit = 5;   /* if Select = 1 then UseDigit = 1 for each digit in s */   con UseDigit_con1 {s in SQUARES, d in DIGITS: digitcount[s,d] > 0}:   Select <= UseDigit ;   /* if UseDigit = 1 then Select = 1 for some s that contains d */   con UseDigit_con2 {d in DIGITS}:   UseDigit <= sum {s in SQUARES: digitcount[s,d] > 0} Select ;   con IsDigitUsedCountTimes_con1 {d in DIGITS}:   sum {c in COUNTS} IsDigitUsedCountTimes[d,c] = 1;   impvar NumTimesDigitUsed {d in DIGITS} = sum {c in COUNTS} c * IsDigitUsedCountTimes[d,c];   con IsDigitUsedCountTimes_con2 {d in DIGITS}:   sum {s in SQUARES} digitcount[s,d] * Select = NumTimesDigitUsed ;   /* if UseDigit = 1 then IsDigitUsedCountTimes[d,c] = 1 for exactly one d > 0 */   con IsDigitUsedCountTimes_con3 {c in COUNTS diff {0}}:   sum {d in DIGITS} IsDigitUsedCountTimes[d,c] = UseDigit ;   /* dummy objective */   min Zero = 0;   num num_solutions init 0;   set SOLUTIONS = 1..num_solutions;   set SQUARES_in_solution {SOLUTIONS};   /* cannot use all three squares from a previous solution */   con Exclude {solution in SOLUTIONS}:   sum {s in SQUARES_in_solution[solution]} Select <= 2;   do while (1);   solve;   if _solution_status_ = 'INFEASIBLE' then leave;   else do;   num_solutions = num_solutions + 1;   put num_solutions=;   SQUARES_in_solution[num_solutions] = {s in SQUARES: Select .sol > 0.5};   print {s in SQUARES_in_solution[num_solutions]} Select;   print {d in DIGITS: NumTimesDigitUsed .sol > 0.5} NumTimesDigitUsed;   end;   end;   create data solution_data from [solution square]={solution in SOLUTIONS, square in SQUARES_in_solution[solution]}; quit; ... View more

oh, hah, I somehow missed the idea that it had to be in sql :smileyblush: variable lists do not work in sql, the best way I know to accomplish the desired outcome it to follow Art's original description. ... View more

data want; set sashelp.class( rename=( age=hour1 height=hour2 weight=hour3 ) ); mean=mean(of hour:); run; ... View more

The following is untested as a warning.  I am also unsure of the performance ramifications of this practice. proc sql; create table have as select libname, memname    from sashelp.vstable   where libname in ( select libname from sashelp.vlibnam where engine='SPDS' ); quit; libname base '/temp'; data _null_; set have;   call execute('proc sql; create view base.v_' || strip(memname) || ' as select * from ' || catx('.', of libname memname) || ';'); run; ... View more

data have; infile cards dsd dlm='09'x; input (level1-level2) (:$20.) featurenbr; cards; Ops          SVCS          23654 Ops          Forms          23654 Ops          Supplies          23654 Ops          Other          23654 People          Salaries          23654 People          Fleet          27589 People          Benefits          27589 People          Other          27589 IT          Equipment          27589 IT          Programming          27589 IT          Phone          36258 Infrastructure          Equipment          36258 Infrastructure          Real Estate          36258 Infrastructure          Other          36258 Ops          SVCS          23654 Ops          Forms          23654 Ops          Supplies          23654 Ops          Other          23654 People          Salaries          23654 People          Fleet          27589 People          Benefits          27589 People          Other          27589 IT          Equipment          27589 IT          Programming          27589 IT          Phone          36258 Infrastructure          Equipment          36258 Infrastructure          Real Estate          36258 Infrastructure          Other          36258 Ops          SVCS          23654 Ops          Forms          23654 Ops          Supplies          23654 Ops          Other          23654 People          Salaries          23654 People          Fleet          27589 People          Benefits          27589 People          Other          27589 IT          Equipment          27589 IT          Programming          27589 IT          Phone          36258 Infrastructure          Equipment          36258 Infrastructure          Real Estate          36258 Infrastructure          Other          36258 Ops          SVCS          23654 Ops          Forms          23654 Ops          Supplies          23654 Ops          Other          23654 People          Salaries          23654 People          Fleet          27589 People          Benefits          27589 People          Other          27589 IT          Equipment          27589 IT          Programming          27589 IT          Phone          36258 Infrastructure          Equipment          36258 Infrastructure          Real Estate          36258 Infrastructure          Other          36258 Infrastructure          Equipment          27589 Infrastructure          Real Estate          27589 Infrastructure          Other          27589 ; run; proc sql; create view vhave as select distinct featurenbr, level1, level2    from have; quit; proc summary data=vhave; var featurenbr; class level2 level1; output out=want(rename=(_type_=level) where=(level>0) drop=_freq_) n=count; run; data level1(drop=level2) level2(drop=level1) cross; set want; select(level);   when(1) output level1;   when(2) output level2;   otherwise output cross; end; drop level; run; ... View more

proc sql;   select level1, level2, count(distinct(featurenbr)) as count      from sample_data     group by level1, level2 quit; ... View more

It has been several years since I used sas on mainframe, and many more since I did so through a mac os, but I remember using the following terminal emulator when I connected to the IBM 3270 mainframe we were using at the time.  It looks like it has been maintained through current versions os mac os too. http://www.brown.edu/cis/tn3270/ From windows I used to be fond of Reflections, but i do not this they make a version for mac.  One other that comes to mind is called ZOC, like Reflections though, is not a free software. ... View more