Hi, Yes, I think what is happening is this: step 1 - the engine sees a union all 2 - it creates a table in the background examining the variables in table 1 3 - the table is assigned as num - default length 4 - the data is inserted 5 - then the next set of data (not complete) KurtBremser's solutions works by creating the table explicitly with the create table statement, and then the temporary table behind the scenes would then be inserted into that.  Same with yours KSharp.  Table creation first then insert the data.  You can fool it with inputs as well: proc sql _method _tree;   create table one_and_two as   select input(put(a,best.),3.) length=3 from data1   union all   select a from data2; quit; So from an SQL point of view, think about what you want the final table to look like, order, lengths, variables etc.  then add the data into it. ... View more

Yes, I suspect he is using the UE edition.  Have a look at: http://support.sas.com/kb/53/333.html ... View more

Couldn't see anything directly wrong with that, maybe try changing base to be 2 or E though I don't think it would have any effect, I would still say its in the data though I can't see as the Queue has broken here so files are not available. ... View more

Hi, Perhaps you need an if/else construct: data f2;      set f1;      pfo=.;      if pfoyr=1991 and index < -1.22 then pfo=1;      else if pfoyr=1991 and -1.22 <= index  <= 2.48 then pfo=2;      else pfo=3;  /* Ommitted the code as all over 2.48 will come out here */ run; ... View more

Check out my post, you cannot use "%" syntax.  SAS is treating the "%ACADEMY%" as a macro variable, so it is trying to find a macro variable name ACADEMY in the macro table and not finding it.  TO use a percentage sign like that, you need to put it within single quotes: '%ACADEMY%'. However saying that, if you have 5000 commercial names then the like syntax is not going to work as there are limitations on how many you can put in.  Try KSharp's code. ... View more

You could apply it to the number variable, or you can create a new character variable and put the number into the Z format.  Or you could apply the format in proc report etc. data want;      set have;      format mobilenumber z11.; run; ... View more

I believe, and correct me If I am wrong, he is doing a merge based on of IND to have based on the text being found, hence there would be an additional column in the output.  The merge is only where both exist.  Nice.  I have run a couple of test though and a straight forward where clause does actually seem to run a fair bit faster than the merge approach: data have;   do aval=1 to 1000000;     mytext="XXXXX ACADEMY"; output;     mytext="YYYY AGENCES YYYY"; output;     mytext="ZZZZ ZZZZZ ZZZZ"; output;     mytext="AAA COMPANY"; output;     mytext="BBBB BBBB"; output;   end; run; data ind; input ind : $40.; cards; COMPANY ACADEMY AGENCES COMPANY ; run; proc sql; create table WANT as select mytext from have  where mytext like '%COMPANY%' or mytext like '%ACADEMY%' or mytext like '%AGENCES%' or mytext like '%COMPANY%'; quit; proc sql; create table WANT as select mytext,ind from have,ind  where mytext contains strip(ind); quit; If you run these a few times I was seeing a difference of maybe 3 seconds real time and 1.9 user cpu time in favour of the where. Note, to the OP - You can't use like "%" as any % within double quotes is treated as a macro variable.  For the like to work you need to use single quotes. ... View more

Cant see your code, the queue seems to be blocked.  Anyways, you are sure the data you are using has no number of 0 or less?  If you have a zero, then maybe consider adding 0.0001 to it so it looks on the output like 0, however behind the scenes its really just over.  ... View more

How would you then get two separate programmers to QC the output?  My opinion is the data should reflect the output as much as possible so a proc compare can be done hence get the data looking like the output then minimal information in the report, then maybe a cosmetic view of the output for review.  Of course if you don't need to validate it then this is irrelevant. ... View more

Ok, I see what you are getting at.  What you have to remember is that SAS produces outputs, it does not run the Excel system.  Therefore the output from SAS will be reproduced each time, there is not real update function.  You can trick it slightly with Excel Libnames, but its not very good.  Yes, proc export can do some basic things as well, but is built to just export a table.  SAS and Excel are very different pieces of software and Excel is really badly misused across the board. So, if you have an Excel template and you need to work to that then you need to find ways, via Excel, to get to that.  Two options stand out - DDE - this is quite old technology that MS invented to allow cross communication between applications and Office.  It has most functionality up to about pre 2000 release of Excel and SAS supports it via DDE commands, here is an example: http://www.nesug.org/proceedings/nesug05/pos/pos15.pdf This may however not do exactly what you want, so the other option I see for you is to export your data from SAS into a readable format, say CSV, then write a set of VBA macros in Excel (not necessarily the same workbook as your output), these macros would load the CSV data, then open your template workbook, post the data where it needs to go, and then update graphs and such like. There is a third option, not something I would recommend.  This is to learn the Open Office structure.  This is the newer Office format, this is a ZIP file (even though the file extension is XLSX or DOCX - just rename to .ZIP), which within contains various folders and XML data.  Other than VBA code, this is how all of your data and graphs etc. are stored, hence if you know how to build this you can pretty much do anything you want, however its not straight forward (and VBA is still compiled so you wouldn't build that). The reason is that you are trying to use a piece of software - Excel - as both a database, calculation and reporting tool.  It is not, its just a glorified calculator.  So IMO you need to either change your approach to use Excel, or change the output requirement to suit the reporting functionality of SAS. A final note, here is an example of VBA code to be run in Excel which opens a list of files from a directory, this could be a good starting point if you wanted to write the VBA import/update macro: Spreadsheet Page Excel Tips: Getting A List Of File Names Using VBA ... View more