Kurt, my data looks like this: 1/1/2015;31/122017;00:00:00;23:59:59 1/1/2015;31/122017;00:00:00;23:59:59 1/1/2015;31/122017;00:00:00;23:59:59 1/1/2015;31/122017;00:00:00;23:59:59 I was just writing it a bit confusing. Anyways, I ve got it partially to work by doing this: Libname Excel1 XLSX "D:\TimeMattersDWH\Import\Startrax\sameday unlimited Handling Steuertabelle.xlsx";
OPTIONS VALIDVARNAME = ANY;
filename tmpout temp lrecl=142;
data _tmp;
set Excel1."Tabelle1"n (FIRSTOBS=1);
FORMAT Export BEST12. 'Security Flat'n BEST12. 'Minimum Labelling'n BEST12. 'Labelling per item'n BEST12.
'Minimum Security'n BEST12. 'Maximum Security'n BEST12. 'Security Flat'n BEST12. 'Security per item'n BEST12.
'Security per kg'n BEST12. 'Ramp per drive Import'n BEST12. 'Other Export Fee'n BEST12. 'Import Flat'n BEST12.
'Import per kg'n BEST12. 'THC Minimum'n BEST12. 'THC Maximum'n BEST12.
'Doc Charge'n BEST12. 'Ramp per drive Export'n BEST12. Transit BEST12.;
run;
proc datasets lib=work memtype=data nolist;
modify _tmp;
run;
proc export data=_tmp
outfile= tmpout
dbms=csv
replace;
putnames=no;
delimiter=';';
run;
DATA &_OUTPUT. ;
LENGTH
Name $ 26
Station $ 3
PartnerID $ 9
Platform $ 3
Type $ 9
'FromWeight'n 8
'ToWeight'n 8
Export 8
'MinimumLabelling'n 8
'Labellingperitem'n 8
'MinimumSecurity'n 8
'MaximumSecurity'n 8
'SecurityFlat'n 8
'Securityperitem'n 8
'Securityperkg'n 8
'RampperdriveImport'n 8
'OtherExportFee'n 8
'ImportFlat'n 8
'Importperkg'n 8
'THCMinimum'n 8
'THCMaximum'n 8
'DocCharge'n 8
'RampperdriveExport'n 8
Transit 8
Currency $ 3
'ValidFrom'n 8
'ValidTo'n 8
'Timefrom'n 8
'Timeto'n 8
Days 8
'Lastupdated'n 8
'Updatedby'n $ 50 ;
FORMAT
Name $CHAR26.
Station $CHAR3.
PartnerID $CHAR9.
Platform $CHAR3.
Type $CHAR9.
'FromWeight'n BEST12.
'ToWeight'n BEST12.
Export COMMA12.2
'MinimumLabelling'n COMMA12.2
'Labellingperitem'n COMMA12.2
'MinimumSecurity'n COMMA12.2
'MaximumSecurity'n COMMA12.2
'SecurityFlat'n COMMA12.2
'Securityperitem'n COMMA12.2
'Securityperkg'n COMMA12.2
'RampperdriveImport'n COMMA12.2
'OtherExportFee'n BEST12.
'ImportFlat'n COMMA12.2
'Importperkg'n COMMA12.2
'THCMinimum'n COMMA12.2
'THCMaximum'n COMMA12.2
'DocCharge'n COMMA12.2
'RampperdriveExport'n COMMA12.2
Transit COMMA12.2
Currency $CHAR3.
'ValidFrom'n DDMMYY10.
'ValidTo'n DDMMYY10.
'Timefrom'n TIME8.
'Timeto'n TIME8.
Days BEST12.
'Lastupdated'n DDMMYY10.
'Updatedby'n $CHAR11. ;
INFILE tmpout
LRECL=142
ENCODING="WLATIN1"
TERMSTR=CRLF
DLM=';'
MISSOVER
DSD;
INPUT
Name : $CHAR26.
Station : $CHAR3.
PartnerID : $CHAR9.
Platform : $CHAR3.
Type : $CHAR9.
'FromWeight'n : BEST32.
'ToWeight'n : BEST32.
Export : BEST32.
'MinimumLabelling'n : BEST32.
'Labellingperitem'n : BEST32.
'MinimumSecurity'n : BEST32.
'MaximumSecurity'n : BEST32.
'SecurityFlat'n : BEST32.
'Securityperitem'n : BEST32.
'Securityperkg'n : BEST32.
'RampperdriveImport'n : BEST32.
'OtherExportFee'n : BEST32.
'ImportFlat'n : BEST32.
'Importper kg'n : BEST32.
'THCMinimum'n : BEST32.
'THCMaximum'n : BEST32.
'DocCharge'n : BEST32.
'RampperdriveExport'n : BEST32.
Transit : BEST32.
Currency : $CHAR3.
'ValidFrom'n : MMDDYY10.
'ValidTo'n : MMDDYY10.
'Timefrom'n : TIME8.
'Timeto'n : TIME8.
Days : BEST32.
'Lastupdated'n : MMDDYY10.
'Updatedby'n : $CHAR11. ;
RUN;
Libname Excel1; I can use it in DIS now, but the file reader gives me an error when I try to open the output table. The log says its all good: NOTE: Variable Importperkg ist nicht initialisiert.
NOTE: The infile TMPOUT is:
Dateiname=D:\SASWork\_TD7232_NI-SASB1_\#LN00026,
RECFM=V,LRECL=142,Dateigröße (Byte)=22008,
Zuletzt geändert=10Jan2017:17:41:42,
Erstellungsuhrzeit=10Jan2017:17:41:42
NOTE: 160 records were read from the infile TMPOUT.
The minimum record length was 108.
The maximum record length was 142.
NOTE: The data set WORK.W4GNSW5 has 160 observations and 33 variables.
NOTE: PROCEDURE| _DISARM| STOP| _DISARM| 2017-01-10T17:41:42,360+01:00| _DISARM| WorkspaceServer| _DISARM| SAS| _DISARM| |
_DISARM| 20533248| _DISARM| 16080896| _DISARM| 11| _DISARM| 11| _DISARM| 90108| _DISARM| 3715043| _DISARM| 0.031250| _DISARM|
0.031000| _DISARM| 1799685702.329000| _DISARM| 1799685702.360000| _DISARM| 0.015625| _DISARM| | _ENDDISARM Now if I try to open it, I get this error: Unable to execute query: SQL-Passthru-Ausdruck enthielt folgende Fehler: ERROR 22-322: Syntax error, expecting one of the following: !, !!, &, *, **, +, ',', -, /, <, <=, <>, =, >, >=, ?, AND, AS, CONTAINS, EQ, EQT, GE, GET, GT, GTT, LE, LET, LIKE, LT, LTT, NE, NET, OR, ^=, |, ||, ~=.... ERROR 22-322: Syntax error, expecting one of the following: eine Zeichenkette in Hochkommata, !, !!, &, (, *, **, +, ',', -, '.', /, <, <=, <>, =, >, >=, ?, AND, AS, BETWEEN, CONTAINS, EQ, EQT, FORMAT, FROM, GE, GET, GT, GTT, IN, INFORMAT, INTO, IS, LABEL, LE, LEN, LENGTH, LET, LIKE, LT, LTT, NE, NET, NOT, NOTIN, OR, TRANSCODE, ^, ^=, |, ||, ~, ~=.... ERROR 76-322: Syntax error, statement will be ignored. But if I look in my SASWork Folder, open the table through "libname a "D:\SASWork\_TD7232_NI-SASB1_\Prc2";, I can see that the data is properly read. I think I could even use the data in further transformations. But I cant look at it.
... View more