topic Re: Import Date and DateTime from Excel via PC Files in SAS Programming

Import Date and DateTime from Excel via PC Files

EvoluZion3 — Fri, 08 Feb 2019 13:06:07 GMT

Hi, I'm trying to use PC FILES (in a PROC IMPORT) to import an Excel spreadsheet which has a Date field and a DateTime field next to eachother. They are different data items.

Unfortunately it appears I can only import either one or the other successfully, resulting in either:

i) using USEDATE=YES I get a correct Date but a DateTime of 1960 plus a few hours.

ii) using USEDATE=NO I get a correct DateTime but a "****" Date, due to it being projected into the year 3 billion.

I have to use PC FILES as the spreadsheet is on a Windows file system but our SAS is installed on Unix, and I'm using SAS 9.4.

I realise of course that I can do USEDATE=NO to get all the data, and then use DATEPART() on just the Date fields to correct it, however my import process is intended to be a shared macro where other users pass in a field list, a format list and a destination dataset name and the import is done for them (this is to help with consistent code across teams), and so I'm trying to avoid the users having to DATEPART() some of their fields after the import has taken place.

If there's nothing native that I can do, then in my macro I may have to do something clever like parse through the field format variable provided by my users into the macro, identify which are just a DATE9, and then do the DATEPART() in an intermediate table, which sounds really heavy going.

Does anyone have any tips?

PROC IMPORT

dbms=excelcs

OUT=WORK.IMPORT

DATAFILE= 'MYEXCEL.XLSX' REPLACE;

server="my_pcfiles_server";

port=1234;

range='Sheet1$';

SERVERUSER="me";

SERVERPASS="foobar";

RUN;

Re: Import Date and DateTime from Excel via PC Files

Kurt_Bremser — Fri, 08 Feb 2019 13:18:46 GMT

Save your Excel data to a csv file, inspect that file, and use the proper informats in a data step to read the data into SAS.

Do NEVER use Excel files if you expect working, reliable data transfer. Never.

Re: Import Date and DateTime from Excel via PC Files

EvoluZion3 — Fri, 08 Feb 2019 13:40:05 GMT

Thanks Kurt. Unfortunately we have no choice over the file 😞

Re: Import Date and DateTime from Excel via PC Files

Kurt_Bremser — Fri, 08 Feb 2019 13:58:24 GMT

Of course you have a choice. Opening and saving to csv with Excel can be scripted.

The important thing is to prevent the guessing of proc import, and that is only possible with a text-based format.

Re: Import Date and DateTime from Excel via PC Files

EvoluZion3 — Fri, 08 Feb 2019 14:08:27 GMT

Hi Kurt, to elaborate on my problem more: the import macro I'm trying to write will be called by various processes which will be scheduled on the SAS Scheduler, and so no Windows/Office-based activity can take place, it all has to be SAS and Unix. Being a shared macro, different team members will be importing lots of different spreadsheets as part of their jobs and so we can't do anything specific to one spreadsheet. Just to add to the problem, our installation of PC Files can't import or export TXT/CSV files. I've no idea why, I blame our I.T. But I know where you're coming from.

Re: Import Date and DateTime from Excel via PC Files

RW9 — Fri, 08 Feb 2019 14:22:31 GMT

"different team members will be importing lots of different spreadsheets" - I would suggest you need to do some sort of system diagnosis. If you have lots of Excel files, lots of users, lots of processes, then you need to have a top down approach. Start by having IT provide an accessible data store, preferably in a cross platform storage method (database, flat text files etc.). Then you would approach from your end to interact with that data source, and the other users interact with the data source their end. Attempting to handle this only at your end will fail. There is a lot of "can't" in the information provide, to which the simple answer is, you either put a whole lot of effort yourself in to try to fix this - not just once, but each and every time, or you "can't" solve the problem because the problem lies at another level.

As for your exact question, no you will need to read it in, then post process it. I will tell you now that every time you run it however it will fail. Excel is a bad data format (no structure or control), and proc import is a guessing procedure.

Re: Import Date and DateTime from Excel via PC Files

EvoluZion3 — Fri, 08 Feb 2019 14:37:19 GMT

Hi RW9, thanks for your reply. I agree with everything you say.

Unfortunately we're a very large company split into different areas who all deal with data, across different methodologies (SAS, Hadoop, Oracle, SQL Server, DB2, Office, IBM Mainframe, etc. etc.) and sometimes it's easier (read: far, far cheaper) to just get hold of an Excel feed from somewhere and ingest it into an intermediary data warehouse which we manage for our area, but unfortunately sometimes when we sniff out a data source we want we can't dictate the nature of the file. This has been manageable up until now when we've hit a couple of spreadsheets which have both Dates and DateTimes, and they've caused us to come unstuck when we've tried to ingest to SAS via PC Files.

Re: Import Date and DateTime from Excel via PC Files

RW9 — Fri, 08 Feb 2019 14:43:53 GMT

Mmm, maybe try a convertor or third party tool like Open Office:

https://unix.stackexchange.com/questions/23726/convert-a-xlsx-ms-excel-file-to-csv-on-command-line-with-semicolon-separated

Could be an option. Afraid the only other option I have for you is to uncompress the xlsx file (they are only ZIP files with a different extension), and then read the plain XML contained therein. Does require knowledge of MS Open Document format to understand the XML (as they make it as complex as possible).

Re: Import Date and DateTime from Excel via PC Files

EvoluZion3 — Fri, 08 Feb 2019 15:30:03 GMT

Thanks, that link was quite interesting.

In my case I don't think it'd help as unfortunately I don't think I can get SAS Scheduler to kick that off, and our PC Files installation can't import CSV files, and I can't transfer a file from Windows to Unix via the SAS Scheduler either. Can you feel my pain yet?

I'll pursue modifying my macro to be clever and do the DATEPART() based on the variables passed in by users of my import macro. If I get it working I'll post the code here for your delight.

Re: Import Date and DateTime from Excel via PC Files

ballardw — Fri, 08 Feb 2019 15:45:30 GMT

@EvoluZion3 wrote:
Hi RW9, thanks for your reply. I agree with everything you say.

Unfortunately we're a very large company split into different areas who all deal with data, across different methodologies (SAS, Hadoop, Oracle, SQL Server, DB2, Office, IBM Mainframe, etc. etc.) and sometimes it's easier (read: far, far cheaper) to just get hold of an Excel feed from somewhere and ingest it into an intermediary data warehouse which we manage for our area, but unfortunately sometimes when we sniff out a data source we want we can't dictate the nature of the file. This has been manageable up until now when we've hit a couple of spreadsheets which have both Dates and DateTimes, and they've caused us to come unstuck when we've tried to ingest to SAS via PC Files.

SAS/Access has modules for accessing Oracle, SQL Server and DB2. If these Excel files are coming from those sources then 1) perhaps SAS/Access may be of interest or 2) each of those will write text files, just talk to whoever exports to Excel and work out an agreement to generate CSV, other delimited or fixed column files which would have such documentation as to variable order, type. lengths and other properties. Which makes writing data steps to read the file formats practically trivial, allows consistent use of proper SAS informat for dates, times or datetime values as needed, assigning variable labels so the data sets help document themselves.

Even Excel and Access will create csv files.

Re: Import Date and DateTime from Excel via PC Files

ErikLund_Jensen — Fri, 08 Feb 2019 17:51:39 GMT

Hi @EvoluZion3

If you have a SAS PC Files server on windows, I think you must have a license to SAS Access to PC Files on your Linux platform as well. We are pestered by the same problem, and we have solved it in the following way:

1. Mount the windows source folder containing the xlsx files on Linux. In my example using this entry in Linux fstab file:

//sas-adm-p.odknet.dk/DWHadmin /mnt/dwhadmin_prod cifs sec=krb5,multiuser,file_mode=0770,dir_mode=0770,nounix,vers=3.0,noserverino

2. Allocate the spreadsheet using the xlsx engine:

libname wbook xlsx "/mnt/dwhadmin_prod/Work/pensionmangler.xlsx";

3. This makes the excel workbook available to your SAS program as a library with the individual sheets as members:

data ind; set wbook.sheetname; run;

Depending on your sheet names, you might have to supply the name as a SAS literal, like: set wbook.'$name'n;

I don't know if your special problem with date/datetime will disappear, but in my experience the xlsx libname is generally more reliable, when it comes to interpreting excel content.

Re: Import Date and DateTime from Excel via PC Files

ErikLund_Jensen — Sun, 10 Feb 2019 10:33:14 GMT

I forgot to mention that you will probably need assistance from your server staff to get a windows folder mounted on Linux in a way that makes it persistent and visible for other users.

And if they do that, they might be persuaded to install a command line file conversion tool like xlsx2csv too. Then you could run the conversion from your SAS program and get a CSV file as input to a data step.

I think the best approach depends on the work flow. If we are talking ad-hoc tasks involving different spreadsheets, the xlsx engine is the easy solution. But reading CSV is much better in a production flow, where spreadsheets are used for regular data transfers, because it gives you full control over column definitions and names.

As RW9 and ballardw and other esteemed advisers points out every time this topic comes up, Excel and SAS is a bad combination. Everything else is better, flat files, csv, ftp.. And twisting arms on external partners could be cheaper, if all the hours wasted on reading spreadsheets are counted too.

Re: Import Date and DateTime from Excel via PC Files

Kurt_Bremser — Sun, 10 Feb 2019 10:55:54 GMT

@EvoluZion3 wrote:
... it all has to be SAS and Unix.

Then there's no place for Excel at all. Excel is a tool for people, it does not lend itself well for automation. On top of that, all tools that SAS provides for Excel interaction rely on guessing, and are therefore not suitable for consistent, repeatable results AT ALL.

Forget Excel, unless you are being paid by your employer for wasting time (and money) and causing problems.

Re: Import Date and DateTime from Excel via PC Files

Tom — Sun, 10 Feb 2019 14:25:00 GMT

From the description it sounds like you have mixed date and datetime values IN THE SAME COLUMN of your Excel sheet.

Can you clarify with an example file that behaves in the way you say?

Are you asking how to distinguish between date (number of days) and datetime (number of seconds) values in a SAS variable?

If your range of possible dates is restricted enough then you might be able to tell just from the magnitude of the number.

data test;
  do year=1960 to 2020 by 10 ;
    date =mdy(1,1,year);
    datetime = dhms(date,0,0,0);
    output;
  end;
  format date datetime comma15. ;
run;

Obs    year               date           datetime

 1     1960                  0                  0
 2     1970              3,653        315,619,200
 3     1980              7,305        631,152,000
 4     1990             10,958        946,771,200
 5     2000             14,610      1,262,304,000
 6     2010             18,263      1,577,923,200
 7     2020             21,915      1,893,456,000

Re: Import Date and DateTime from Excel via PC Files

ErikLund_Jensen — Sun, 10 Feb 2019 16:20:06 GMT

Hi Tom

Your post made me look closer on the date range, because I feared a flaw in a program working on similar principles.

SAS acceps dates between January 1, 1582 and December 31, 9999. The corresponding internal values are:
date: -138061 to 2936547
datetime: -11928470400 253717747199

This makes values in the interval -138061 to 2936547 ambiguous. It could be a datetime between
30dec1959:09:38:59 and 03feb1960:23:42:27 or a date in the whole legal day span.

Outside that interval, there is no ambiguity, so NOT (-138061 <= value <= 2936547) will be true for datetime values only, so if it is certain that the the transaction or whatever occurred within the last 58 years, it should be safe to distinguish using the magnitude.

Re: Import Date and DateTime from Excel via PC Files

EvoluZion3 — Mon, 11 Feb 2019 09:31:43 GMT

Hi Tom, no they are two different fields, one with Dates and one with DateTimes. "Date field and a DateTime field next to eachother".

Re: Import Date and DateTime from Excel via PC Files

EvoluZion3 — Mon, 11 Feb 2019 09:34:58 GMT

Hi ErikLund, thanks for your reply. I've tried that (fortunately we are able to LIBNAME from our Unix SAS installation into a Windows Excel file already) and unfortunately it hasn't worked - it classed all Date and DateTime fields as a Date, and therefore truncated the Time from the DateTime.

Re: Import Date and DateTime from Excel via PC Files

EvoluZion3 — Mon, 11 Feb 2019 09:46:26 GMT

Morning everyone. Thanks for your input. After a bit of faffing around I did manage to find a tactical solution to this.

I took my Import from Excel macro:

%MACRO IMPORT_EXCEL_PCFILES(FULL_PATH, TAB_NAME, OUTPUT_LOCATION, FIELD_DEFINITIONS, FIELD_FORMATS);

	%PUT ### Call to IMPORT_EXCEL_PCFILES;
	options nomprint nosource2; /* MUST go before any text files with passwords are included! */
	%include '~/uid_pwd.sas' /nosource;

	/*
		FIELD_DEFINITIONS and FIELD_FORMATS are optional. Omitting them trusts SAS to work out the field formats for you, based on Excel metadata.
		You generally only need to provide some FIELD_FORMATS to display DATEs correctly.
		To import a spreadsheet tab put a $ after the TAB_NAME. To import just a named range do not put a $ after the TAB_NAME.

		Example of usage

			%LET FIELDS =	MYSTRING $6
										VARIABLE_NAME $32
										VARIABLE_NUM 8
										BIRTHDAY 8
										EXACTLY_WHEN 8;
			%LET FORMATS = BIRTHDAY DATE9.
										 EXACTLY_WHEN DATETIME19.;
			%IMPORT_EXCEL_PCFILES("\\WindowsServer\My_Metrics.xlsb", "Variables$", WORK.MY_VARIABLES, &FIELDS., &FORMATS.);
	*/

	PROC IMPORT 
		dbms=excelcs
		OUT=WORK.______TMP000000______imp_exc_pcf
		DATAFILE= &FULL_PATH. REPLACE;
		server="our_pcfiles_server";
	  port=1234;
		range=&TAB_NAME.;
	  SERVERUSER=&uname.;
	  SERVERPASS="%sysfunc(dequote(&ad_pwd.))";
	RUN;

	DATA &OUTPUT_LOCATION.;
		LENGTH &FIELD_DEFINITIONS.;
		SET WORK.______TMP000000______imp_exc_pcf;
		FORMAT _all_;
		FORMAT &FIELD_FORMATS.;
	RUN;
	
	%_eg_conditional_dropds(WORK.______TMP000000______imp_exc_pcf);

%MEND;

...and added in code to USE_DATE=NO, iterate through the FIELD_FORMATS variable passed in by the user, and create an UPDATE...SET...x=DATETIME(x) where the date formats are DATE9:

%LET FULL_PATH = '\\WindowsServer\test.xlsx';
%LET TAB_NAME = 'Sheet1$';
%LET OUTPUT_LOCATION = WORK.OUTPUT;
%LET FIELD_DEFINITIONS = NAME $16
                                                                                         MYDATETIME 8
                                                                                         MYDATE 8
                                                                                         MY_ID 8
                                                                                         LAST_TRANX 8
                                                                                         BIRTHDAY 8
                                                                                         ANOTHER_DATE 8;
%LET FIELD_FORMATS =       MYDATETIME DATETIME19.
                                                                           MYDATE DATE9.
                                                                           LAST_TRANX DATETIME19.
                                                                           BIRTHDAY DATE9.
                                                                           ANOTHER_DATE DATE9.;


PROC IMPORT 
       dbms=excelcs
       OUT=WORK.______TMP000000______imp_exc_pcf
       DATAFILE= &FULL_PATH. REPLACE;
       server="our_pcfiles_server";
  port=1234;
       range=&TAB_NAME.;
       USEDATE = NO;
       SERVERUSER=&uname.;
  SERVERPASS="%sysfunc(dequote(&ad_pwd.))";
RUN;

DATA &OUTPUT_LOCATION.;
       LENGTH &FIELD_DEFINITIONS.;
       SET WORK.______TMP000000______imp_exc_pcf;
       FORMAT _all_;
       FORMAT &FIELD_FORMATS.;
RUN;


/* Date vs DateTime problem - SAS can't import an Excel if there's a Date field and a DateTime field (by default it ignores the Time part of a DateTime), so we need to override this using the USEDATE=NO option to import
       everything as a DateTime (which breaks normal Dates by pushing them to the year x million), scan through the field formats in FIELD_FORMATS by the user, identify any Dates, then iterate through the intermediate table
       and apply a DatePart() to correct the Dates. */

/* i) Split FIELD_FORMATS down into a table, allowing us to identify any DATE9 fields */
DATA WORK.______TMP000001______imp_exc_pcf;
   length word $32;
   do until(word=' ');
      count+1;
      word = scan("&FIELD_FORMATS.", count);
      output;
   end;
RUN;

/* ii) The above table has fieldnames and field formats alternately, so tidy this up and create a new temporary table */
PROC SQL;
       CREATE TABLE WORK.______TMP000001______imp_exc_pcf AS
       SELECT
              FIELDS.WORD AS FIELDNAME
       FROM
              (
                     SELECT
                           count - 1 AS ROW_ID
                     FROM
                           WORK.______TMP000001______imp_exc_pcf
                     WHERE
                           word = 'DATE9'
              ) DATE9
              INNER JOIN WORK.______TMP000001______imp_exc_pcf FIELDS
                     ON DATE9.ROW_ID = FIELDS.COUNT;
QUIT;

/* iii) Iterate through the above table and dynamically create a line of SQL which performs an UPDATE and a DATEPART() on the relevant fields */
%MACRO PARSE_AND_DATEPART;
       PROC SQL noprint; SELECT COUNT(*) INTO :import_excel_fields_datepart FROM WORK.______TMP000001______imp_exc_pcf; QUIT;
       %if &import_excel_fields_datepart. > 0 %then %do;
              %LET import_excel_fields_datepart_sql = UPDATE &OUTPUT_LOCATION. SET ;
              %do import_excel_fields_datepart_l = 1 %to &import_excel_fields_datepart.;
                     PROC SQL noprint; SELECT FIELDNAME INTO :import_excel_fields_datepart_f trimmed FROM WORK.______TMP000001______imp_exc_pcf WHERE MONOTONIC() = &import_excel_fields_datepart_l.; QUIT;
                     /*%put &import_excel_fields_datepart_l.: &import_excel_fields_datepart_f.;*/
                     %if &import_excel_fields_datepart_l. < &import_excel_fields_datepart. %then %do; %LET import_excel_fields_datepart_c = ,; %end; %else %do; %LET import_excel_fields_datepart_c =  ;  %end;
                     %LET import_excel_fields_datepart_sql = &import_excel_fields_datepart_sql. &import_excel_fields_datepart_f. = DATEPART(&import_excel_fields_datepart_f.) &import_excel_fields_datepart_c. ;
              %end;
              /*%put &import_excel_fields_datepart_sql.;*/
              PROC SQL; &import_excel_fields_datepart_sql.; QUIT;
       %end;
%MEND;
%PARSE_AND_DATEPART;

I know I know, it's horrible, it's not finding the perfect solution, but so far it's working.

Re: Import Date and DateTime from Excel via PC Files

Patrick — Mon, 11 Feb 2019 10:40:48 GMT

@EvoluZion3

Just to add a few remarks:

- Excel is not a reliable format for data exchange and you'll have other problems in the future. But I understand that realities sometimes don't give us a choice.

- If you have a reasonable recent SAS version then I'd use the XLSX engine to import Excel files. This engine comes as part of SAS/Access Interface to PC Files. The XLSX files can "live" anywhere to where the SAS Server has access to. It doesn't need to be a mapped Windows file system.

- I understand that you can't convert the Excel to CSV by calling MS Office components in a Linux environment. What could be working though to convert the Excels to Text is Tika. https://tika.apache.org/1.18/formats.html#Microsoft_Office_document_formats

SAS uses Tika for some of its products so there is a good chance that it's already available in your environment. If not then it's just a .jar file which you simply can copy onto your environment without having admin rights. You just need the right JRE installed - but that's likely available. I've used Tika in past projects and it's a really great tool which you can call out of SAS without any problems (as long as option XCMD is set). The advantage of reading data from text files is that you've got full control how you read the data.

- That you can't read text files doesn't make any sense. That's Foundation SAS (Base SAS) functionality and I've never seen that not working anywhere. If you've got such an issue then I'd strongly suggest you raise a SAS TechSupport ticket so that you get leverage to get this resolved - or guidance what you need to change in your code to make this work.

Re: Import Date and DateTime from Excel via PC Files

RW9 — Mon, 11 Feb 2019 11:23:49 GMT

That Tika Jar system seems quite interesting and powerful. Unlike ordinary command line converter programs, which would need command line access, this could theoretically be run just within SAS using the declare javaobject:

http://support.sas.com/documentation/cdl/en/lecompobjref/63327/HTML/default/viewer.htm#n0cdvh1d0v4g5rn0z9dnihwtatnx.htm

Could it not? I wonder if anyone has tried doing such a thing, I could not find any examples online, and I think it would be useful to see a full working example of something like this. The documentation on it is also sorely lacking any kind of solid examples:

http://support.sas.com/documentation/cdl/en/lecompobjref/63327/HTML/default/viewer.htm#n0cdvh1d0v4g5rn0z9dnihwtatnx.htm

If I get some time I will have a fiddle around with it myself.