Can you please tell us more about this SAS application? Which SAS products is in use? How is the users supposed to interact with the data? Is reports, cubes or ad-hoc queries using Enterprise Guide? The obvious advice is to transport as little data as possible over your WAN. Regards, Linus ... View more

There is a similar thread in the "SAS programming" forum: http://support.sas.com/forums/thread.jspa?threadID=3679&tstart=0 /Linus ... View more

Can't you do a Select distinct on tableA, the do a ordinary inner join? Then the SQL optimizer may use an indexed join strategy, even a hash join if the result set from tableA is small enough. Regards, Linus ... View more

Please share your proc format code, it's easier to help then. /Linus ... View more

Somthing like this (untested): %macro export(table); %let dsid=%sysfunc(open(testing,i)); %let iswhere=%sysfunc(attrn(&dsid,nobs)); %if &ISWHERE ne 0 %the %do; proc export data=&TABLE OUTFILE="/SAS/SGH/PFS_CCR/Collection/Risk12/testing&d5.xls" DBMS=xls REPLACE; RUN; %end; %mend export; %export; /Linus ... View more

Assuming that target was misspelled taget. Well that depends on your needs.You can define you own libraries through New Library Wizard. I suggest that you engage as DW architect who can suggest what types of libraries you'll need, and what engine to use. Regards, Linus PS This wasn't really an AMO question. On the other hand there is no Data Integration forum, which would be great to have! ... View more

In your case, try to put your Sybase statements within execute ( ) blocks within proc sql: PROC SQL; connect to sybase ...; execute (SET TEMPORARY OPTION Temp_Extract_Column_Delimiter = ',') by sybase; execute (SET TEMPORARY OPTION Temp_Extract_Row_Delimiter = '') by sybase; execute (SELECT * FROM mydatabasetable) by sybase; disconnect from sybase; QUIT; This might work from a SAS syntax view. But if this will create your external file or not, you'll have to discuss with some Sybase people. I'm especially suspicious against the select statement. In my eyes Sybase might try to return the result to SAS, which is not feasible when using execute blocks. Regards, Linus ... View more

Try %let todaydate = %sysfunc(putn("&SYSDATE9"D,ddmmyy.)); Regards, Linus ... View more

If you have an index on your date var, be sure to pre calculate and store the result of the INTNX expressions in macro variables, otherwise you won't hit the index when using WHERE. Regards, Linus ... View more

It depends on what kind of flexibility/automation you are looking for. Here is a suggestion for some steps: 1. Find out the names of the different directories. For this you can use the functions DOPEN, DNUM and DREAD. 2. Using the output from DREAD to do a CALL EXECUTE 3. The macro called from 2 assigns an aggregate fileref, or loops through each file using directory functions again. Then read each file. Move each file. Hope this helps, Linus ... View more

I didn't know about this one, really neat. Still, I can't see how you can easily sort a whole table, at least if it has many rows and columns. The question is what you want to accomplish. /Linus ... View more

Maybe this is a little closer...: proc sql; create table NEWTABLE as select A.*, case B.partno when . then 'N' else 'Y' end as updated from A left join B on A.partno = B.partno ; quit; Regards, Linus ... View more

I don't know the neatest way in EG to do this, but you can use either DICTIONARY.TABLES or the ATTRN function to find out whether your input table(s) has observations or not, and combine this with some conditional logic using macro programming. Regards, Linus ... View more