Hi chitra, I am sending proc import code, I tried datastep with infile and I failed badly. I am adding excel file, Thanks, DM'LOG;CLEAR;OUT;CLEAR'; PROC IMPORT OUT= WORK.work DATAFILE= "C:\Users\SAS\Desktop\DUMMY DATA.xlsx" DBMS=excel replace; /* datarow=20;*/ /* DATAROW=19;*/ RANGE="Accounting$"; GETnaMES=yes; MIXED=yes; SCANTEXT=YES; USEDATE=YES; SCANTIME=YES; RUN; data work.daya; retain start_date; length START_DATE 8 TNUMBER $64. DATE_TIME $22. DUES $35. TIN_NUMBER $15. TELLER $6. SELLOR $30. BALANCE $15. TRDATE $10. SHOP $75. SNUMBER $25. ADDRESS $75. TOWN $50. STATE $10. ZIPCODE $10. DBALANCE 8; set work (rename=(F1=TNUMBER F2=DATE_TIME F3=DUES F4=TIN_NUMBER F5=TELLER ACCOUNTING_FOR_DATABASE=SELLOR F7=BALANCE F8=TRDATE datetime__07_11_2016_09_30_55=SHOP F10=SNUMBER F11=ADDRESS F12=TOWN F13=STATE F14=ZIPCODE)); if _n_=6 then start_date=input(compress(scan(TNUMBER,2,':')),mmddyy10.); if _n_>=20; DBALANCE=input(COMPRESS(BALANCE,'$,'),best.); dtrdate=input(trdate,mmddyy10.); ddate_time=input(compress(scan(date_time,1," ")), mmddyy10.); format start_date dtrdate ddate_time mmddyy10.; attrib _all_ label=" "; run;
... View more
