I need to backfill a data mart with data from 4000 historic spreadsheets. The following code runs as part of a DI Studio job. proc import dbms=xls datafile="Report.xls" out=&_OUTPUT replace; sheet="Cumulative Daily Flows"; getnames=no; datarow=3; guessingrows=32767; run; The code runs without any warnings or errors but truncates the values in a significant number of cells. For example, in the workbook we have a cell containing a formatted value of (10,037,753) with the actual cell value being -10037753.42.  Following the import into SAS the corresponding unformatted value in the output dataset is -10037753.4 (losing the final decimal place).  It seems to happen when the length of the unformatted cell value, including the ‘-‘ and ‘.’ is 12 characters or more and is not an integer, and occurs for both positive and negative numbers. I’ve tried setting the relevant fields to numerics and character with a variety of informats in both the input file and output table metadata but in all cases the truncation occurs. I’ve also set guessingrows to the maximum value but, again, this makes no difference. The only way I that I’ve been able to make this work properly so far is to paste the values from the original report into a completely new workbook.  In the original workbook, the cells have a custom format applied to allow brackets to indicate negative values so I thought that this may be the cause, but resetting all cell formats to “General” (which is what they are in the new xls) in the original report again makes no difference. Now, I could write some VBA to copy the contents of 4000 spreadsheets into new documents but this would take forever to run and I really don't want to do this unless I absolutely have to.  I don't have the option of using a datastep approach using the Excel libname as this doesn't work on our servers due to driver incompatibility. Any suggestions would be greatly appreciated. ... View more

We have a reporting process that is being migrated from another technology onto our SAS environment. Part of this process generates summary data and exports it into a specific range of cells in a pre-existing Excel spreadsheet (actually into a copy of a master template file). The number of generated values is unlikely to change and so will always be written to the same cells, e.g. B1:B12 on sheet1 of the relevant workbook. I believe that there are a couple of obvious ways that we could replicate this functionality programmatically in SAS but neither appear viable due to on-site technical constraints. 1. I think that the most straightforward way would be via the Excel libname engine.  However, this is not available to us due to 32/64 bit incompatibility issues with Excel on the server. 2. Although probably less straighforward (and possibly a tad outdated) we also attempted utilising DDE filename statement options.  However, this requires the X command options to be enabled. The data export jobs need to run as part of a scheduled LSF flow on a BI server (version 9.2) where command line options are, by default, switched off (and due to site security policy it is unlikely that this will be changed).   Can anyone suggest an alternative approach? Thanks, in advance, for any suggestions. ... View more

I have a table that contains strings containing dates in a variety of formats (e.g. some have values like '20110701', others like '01Jul11') and need to be able to dynamically reformat back to the SAS numeric equivalents. These strings are not in a consistent format so I can't use an input statement with a hard-coded informat to do this. I am able to derive the informats that I'd need to use for each row but still can't reformat without errors. For example, if I create a single row equivalent using the following data step: data tmp;    Char_date='20110701';    Date_format='yymmdd8.'; run; What I actually want is to be able to derive a third variable, Num_date, from the values in Char_date and date_format, i.e. doing the equivalent of an input statement. input(Char_date,date_format);  doesn't work because Date_format is parsed as a character string rather than the keywords for a SAS format. As an alternative, I've tried to create macro variables for the format in each row using the following. data _null_;   set tmp;   call symputx('DateFormat'||strip(_N_),Date_format); run; However, when I attempt to dynamically reference these macro variables, then I get the same problem as above: data tmp2;   set tmp; Num_Date=input(Char_date,symget('DateFormat'||strip(_N_))); run; With the value returned by the call to symget being a string, rather than the format value.  I think that this approach can work because if I hard code the macro variable reference as follows: data tmp2;   set tmp;   Num_date=input(Char_Date,&DateFormat1); run; Then I get the results that I'd expect. So, it seems that I'm missing something in how to dynamically reference the macro variables created in the null data step above.  I've looked at this so often now that I'm probably missing something really obvious so apologies if this is the case.  Can anyone tell me what I'm doing wrong? Thanks for your help. ... View more