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.
Click image to register for webinar
