Thanks jklaverstijn, here is code i wrote *proc import datafile="C:\Attrition Data\1607\Active_Employees.xlsx" dbms=xlsx out=abc replace; run;
options validvarname=any;
data at;
LENGTH
EmployeeCode $ 6
DateofJoin 8
DateOfRelieving 8;
FORMAT
EmployeeCode $CHAR6.
DateofJoin DATE9.
DateOfRelieving DATE9.;
INFORMAT
EmployeeCode $CHAR6.
DateofJoin DATE9.
DateOfRelieving DATE9.;
infile 'C:\Attrition Data\1607\Book1.xlsx'
LRECL=915
DLM='7F'x
MISSOVER
DSD ;
INPUT
EmployeeCode : $CHAR6.
DateofJoin : BEST32.
DateOfRelieving : ANYDTDTE10.;
RUN; table i am using is Book1 EmployeeCode DateofJoin DateOfRelieving R1 1/1/2000 NULL R2 1/2/2000 10/12/2012 R3 1/3/2000 10/12/2012 R4 1/4/2000 NULL R5 1/5/2000 NULL R6 1/6/2000 10/9/2012 R7 1/7/2000 10/15/2012 R8 1/8/2000 NULL R9 1/9/2000 NULL R10 1/10/2000 NULL R11 1/11/2000 10/15/2012 R12 1/12/2000 NULL R13 1/13/2000 NULL R14 1/14/2000 10/15/2012 R15 1/15/2000 NULL and the output i am getting is for data step >at EmployeeCode DateofJoin DateOfRelieving PK . . output for proc import is (whre dates are from 01/01/1900 hence i can not do post processing after import.) R1 01/01/2000 NULL R2 01/02/2000 41194 R3 01/03/2000 41194 R4 01/04/2000 NULL R5 01/05/2000 NULL R6 01/06/2000 41191 R7 01/07/2000 41197 R8 01/08/2000 NULL R9 01/09/2000 NULL R10 01/10/2000 NULL R11 01/11/2000 41197 R12 01/12/2000 NULL R13 01/13/2000 NULL R14 01/14/2000 41197 R15 01/15/2000 NULL and expected output is EmployeeCode DateofJoin DateOfRelieving R1 1/1/2000 . R2 1/2/2000 10/12/2012 R3 1/3/2000 10/12/2012 R4 1/4/2000 . R5 1/5/2000 . R6 1/6/2000 10/9/2012 R7 1/7/2000 10/15/2012 R8 1/8/2000 . R9 1/9/2000 . R10 1/10/2000 . R11 1/11/2000 10/15/2012 R12 1/12/2000 . R13 1/13/2000 . R14 1/14/2000 10/15/2012 R15 1/15/2000 .
... View more