Have him confirm that the issue is not within the sql server database itself, as SAS is reporting it is.  Have him check the table and indexes for fragmentation.  Give him the information from your log WARNING: During read: [Microsoft][ODBC SQL Server Driver]String data, right truncation ERROR: CLI cursor fetch error: [Microsoft][ODBC SQL Server Driver][SQL Server]Could not continue         scan with NOLOCK due to data movement. You query: data temp;set qb.client;keep ID adddate;run; is equivalant to select id, adddate from client; Check that NOLOCK should be part of this query, if not check whether this is set by database or odbc bridge or possibly SAS (but I don't think it would be). To get more information about the query's execution from sas use the sastrace option. ... View more

At the OS level just ensure that the password file is only readable by the user or group of users who are expected to utilize it, and you have as close to a secure facility as I know how to achieve for a connect statement. ... View more

This is a pretty bad SQL server error message usually caused I believe by highly fragmented tables and or indexes. If you have one consult with your SQL sever DBA. Also queries using nolock are usually not desirable.  Is this SQL server utilized as a warehouse for data? Is it denormalized? ... View more

This suggesttion will also work well, however if you are going to use it inside a select statement to dbs like oracle you will need another one where the in="'My password'"  Also I am pretty sure that certain conditions of the sastrace option will still show the unmasked password.  This is just an assumption though. ... View more

The %syslput and %sysrput are for sharing variables between mp-connect processes.  Which I do not think the op is using? ... View more

Sorry I forgot that one piece. During the call of &user or &pass you can replace with %sysfunc(symget(user)) etc. However all methods will still show masked data in the log if certain suboptions of sastrace are used. options mlogic mprint macrogen symbolgen; data _null_;   input (uid pwd) ($);   call symput('user',"'"||uid||"'");   call symput('pass',"'"||pwd||"'");   cards; a1 abcde ; run; options nosymbolgen; proc sql;   connect to oracle user=&user /* or %sysfunc(symget(user)) */ pass=&pass /* or %sysfunc(symget(pass)) */ path=mypath; create table want as select * from connection to oracle   ( select *       from mytable      where uid=&user /* or %sysfunc(symget(user)) */ and pwd=&pass /* or %sysfunc(symget(pass)) */); disconnect from oracle; quit; options symbolgen; I do believe that any method of masking will be foiled by utilizing the sastrace option for any of it's routines that return submitted SQL statements. http://support.sas.com/documentation/cdl/en/acreldb/63647/HTML/default/viewer.htm#a000433982.htm ... View more

What are you connecting to when running the query?  For example, if you are connecting to oracle the query would not work becuase the character strings you are trying to pass the where clause are not encased in single quotes.  Try doing this: %let user=user; %let pass=pass; data _null_;   call symput('user',"'"||"&user"||"'"); call symput('pass',"'"||"&pass"||"'"); run; proc sql;   connect to oracle user=&user pass=&pass path=mypath; create table want as select * from connection to oracle   ( select *       from mytable      where uid=&user and pwd=&pass ); disconnect from oracle; quit; ... View more

I also simplified my original approach: data foo; 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    do; j+1;     call symput('vmax',j);     output;    end; end; keep ps; run; proc transpose data=foo out=bar(drop=_:); run; data foobar; set bar; array col[&vmax]; do i=1 to comb(&vmax,3);   call allcomb(i,3,of col );   nbrs=cats(of col1-col3);   array nfreq[5] (0 0 0 0 0);   yes=0;   do ii=1 to dim(nfreq);    nfreq[ii]=length(compress(nbrs,ii,'k'));    if 0<nfreq[ii]<=5 and nfreq[ii] ne ii then yes+1;    if nfreq[ii]=1 then key=ii;   end;   call sortn(of nfreq );   if cats(of nfreq )='12345' and yes=5 then output; end; keep key col1-col3; run; proc sql; select 'answer is: ', col1, col2, col3    from foobar   group by key having count(*)=1; quit; /*                                                              COL1      COL2      COL3                                              -----------------------------------------                                              answer is:      12321     33124     34225 real time 0.01 seconds cpu time 0.00 seconds */ ... View more

Very nice utilization of proc fcmp for this problem.  By simplifying the rules to fit the question more closely, i.e. we know the digits can only be 1,2,3,4,5 we can cut the runtime to nearly instant. /* instead of 217 possibilities I only have 9 valid perfect squares to choose from */ data one; array sd[5]; do i=100 to 236;   v=i**2;   do d=1 to dim(sd);    sd =substrn(v,d,1);   end;   if max(of sd1-sd5)<=5 and min(of sd1-sd5)>0 then output; end; keep v; run; proc fcmp outlib=work.func.quiz; /* returns > 1000 if any condition is not met */ function usedOnceDigit(v1, v2, v3);   length v $15 nDigits usedOnceDigit nUsedOnceDigits i j 8 c $1; /* I only need to perform from 1 to 5 instead of 0 to 9 */   array freq[5] (0 0 0 0 0);    v = cat(v1, v2, v3);    nDigits = 0;    usedOnceDigit = .;    nUsedOnceDigits = 0;   do i = 1 to 5;    c = substr("12345", i, 1);    freq = 15 - length(compress(v, c));    if freq > 0 then     do;        if i = freq then return(1005);      if freq > 5 then return(1004);      if index(v, substr("12345", freq, 1)) = 0 then return(1004);      nDigits + 1;      if nDigits > 5 then return(1002);       do j = 1 to i-1;        if freq > 0 and freq = freq then return(1003);       end;      if freq = 1 then       do;        usedOnceDigit = i;        nUsedOnceDigits + 1;       end;     end;   end;   if nDigits < 5 then return(1002);   if nUsedOnceDigits ^= 1 then return(1006);   return(usedOnceDigit); endsub; quit;    %let cmplib = %sysfunc(getoption(cmplib));    options cmplib = (work.func &cmplib);       proc sql;          select "answer is: ", v1, v2, v3          from (             select o1.v as v1, o2.v as v2, o3.v as v3                  , usedOnceDigit(o1.v, o2.v, o3.v) as usedOnce             from   one as o1, one as o2, one as o3             where  o1.v < o2.v and o2.v < o3.v                and calculated usedOnce < 1000          )          group by usedOnce          having count(*) = 1;       quit;             options cmplib=(&cmplib); Very nice program.  Here were the run stats from my machine (64-bit linux): real time 0.02 seconds cpu time 0.01 seconds ... View more

There is a balance in the performance offered by tagsorting.  If you use the tagsort option you remove the ability for the sort to be multithreaded.  So if your performance is hindered by I/O avaiablility for your utility files from the sort, then tagsort is a good option.  If you are not bounded by I/O or disk space for these files then you will likely see hindered sort performance.  You could try allocating additional memory to the sort procedure (see the sortsize option and other options in the memory/performace groups).  On my system, with default memory options a table with 4,000 columns and 1,000,000 obs took about 5 minutes with a lot of system competition.  However if I run the same sort with the tagsort option on it took over 10 minutes. ... View more

Are you confusing a hash join with a type of sort?  Are you wanting to create a new key as a hash value of the concatenation of the name and date fields?  Or something else? A sort of a file with only 72 variables and 1,200,000 observations should be very quick already...  What kind of system are you using?  You could try using the tagsort option. ... View more

filename out1 '/temp/tmp1.csv'; filename out2 '/temp/tmp2.csv'; data s1; infile cards dsd dlm='09'x; input (date px_last px_low px_high volume) (:$20.); file out1 dsd dlm=',';   put (date px_last px_low px_high volume) (:$20.); cards; #NAME?          40.4688          40.1875          41.7813          2705600 1/4/2000          40.6563          40.1875          40.9063          4112400 1/5/2000          43          40.5          43.25          5844600 1/6/2000          42.4375          42.4063          43.1875          8595200 1/7/2000          42.625          42.2813          43.4375          7026800 ; run; data s2; infile cards dsd dlm='09'x; input (date px_last px_low px_high volume) (:$20.); file out2 dsd dlm=',';   put (date px_last px_low px_high volume) (:$20.); cards; #NAME?          1160.9274          1150.456          1200.0206          151718 1/4/2000          1101.5896          1098.0991          1140.6827          340523 1/5/2000          1103.6838          1080.6468          1116.2495          248678 1/6/2000          1139.2866          1100.1934          1157.437          261000 1/7/2000          1223.0576          1153.2484          1227.9443          311985 ; run; /* this is the part that matters to your situation */ filename in ('/temp/tmp1.csv' '/temp/tmp2.csv'); data have; length _file $256; infile in dsd dlm=',' filename=_file; input date :??mmddyy8. (px_last px_low px_high) (:8.4) volume :7.; ticker=scan(scan(strip(_file),-1,'/'),1,'.'); run; /* end */ x 'rm -rf /temp/tmp*.csv'; ... View more

You are correct again, I mispoke.  You use the from statement as usualy and embed the library reference data with the using clause. %let spds_lib=SPDS; proc sql; create table have as select libname, memname    from sashelp.vstable   where libname in "&spds_lib"; 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) || ' using libname in sasspds "domain-name" server="server" user="userid" password="password";'); run; ... View more

Thanks Peter, this is a good point.  Instead of the from statement the using clause could be used inorder to assign the library upon use of the view instead of needing to be a preexisting definiton.  This much better fits the desired functionality of your views. ... View more