Below is my code. Just I do a direct hit to SQL server database and create a SAS dataset and then load into a file.
In the intermediate SAS dataset, I could see all the columns are populated correctly but not in the .txt file.
proc format ; picture datetimefmt low-high = '%Y-%0m-%0d %0H:%0M:%0s' (datatype=datetime ) other = .; run;
proc format ; picture timefmt low-high = '%0H:%0M:%0S' (datatype=time ) other = .; run;
proc format ; picture datefmt low-high = '%Y-%0m-%0d' (datatype=date) other = .; run;
proc sql; connect to SQLSVR as SRC (datasrc="&Datasrc" AUTHDOMAIN="XXX"); exec ( SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED ) by SRC; create table WORK.TEMP as select CONTEXTID , RREF , DTTM format datetimefmt26.6, TS length=25 format=$25. , MONOTONIC() AS ROW_NUM, input("&SYSDATE9 &SYSTIME",DATETIME25.6) as STG_LOADTM format datetimefmt26.6 from connection to SRC ( select * from &Schema..REL ); disconnect from SRC; alter table WORK.TEMP modify ts char(16) informat=$16. format=$16.; quit;
%let LOG_DIR=%unquote(%str(%')&_LOGDIR%str(%')); %let SRC_FEED=TEMP.txt; %let SOURCE_FILE=%unquote(%str(%')&_SRCDIR/&SRC_FEED%str(%'));
proc export data=work.TEMP outfile="&SOURCE_FILE" dbms=dlm replace; PUTNAMES=NO; delimiter='|'; run;
... View more