Søren,

I probably am making things more complicated than necessary, but I think that your suggestion would be oversimplifying the problem statement. The complications were added so that two specific Excel formats could be differentiated, namely m/d/yyyy and d/m/yyyy.

Art ---------- On Mon, 30 Nov 2009 01:54:12 -0500, S=?ISO-8859-1?Q?=C3=B8ren?= Lassen <s.lassen@POST.TELE.DK> wrote:

>Art, >You can download a complete help file of Excel 4.0 macro functions >from http://support.microsoft.com/kb/q128185/. > >Though I cannot help thinking that you are making things a little more >complicated than necessary - would something like this not work: > >options noxsync noxwait xmin; >filename sas2xl dde 'excel|system'; > >/*Open Excel*/ >data _null_; > length fid rc start stop time 8; > fid=fopen('sas2xl','s'); > if (fid le 0) then do; > rc=system('start excel'); > start=datetime(); > stop=start+10; > do while (fid le 0); > fid=fopen('sas2xl','s'); > time=datetime(); > if (time ge stop) then fid=1; > end; > end; > rc=fclose(fid); >run; >data _null_; > file sas2xl; > put '[open("c:\datetest.xls")]'; >run; > >filename xldates dde 'excel|[datetest.xls]sheet1!r2c1:r15c1' notab; > >data one; > infile xldates truncover; > input datetxt $char40.; > /* and then you can use your SAS code to convert datetext to a date */ >run; > >Regards, >Søren > > >On Sun, 29 Nov 2009 12:30:03 -0500, Arthur Tabachneck <art297@NETSCAPE.NET> >wrote: > >>I'll try to make this request brief, but what appeared at first to be a >>simple question is quickly turning into a dissertation. >> >>I'm working with a very short excel file (c:\DateTest.xls) that appears as >>follows: >> >>date >>jan.1, 2009 >>Friday, January 02, 2009 >>Saturday, January 03, 2009 >>01/04/09 >>01/05/09 >>01/06/09 >>7/1/2009 >>8/1/2009 >>9/1/2009 >>jan-10-09 >>11-Jan >>january-12-2009 >>january/13/2009 >>2009-jan-14 >> >>The 14 rows represent Jan 1, 2009 thru Jan 14, 2009 in various formats. >> >>Even if one licences SAS/ACCESS for pc file formats, proc import can't >>directly interpret all of the rows correctly. I was able to get it to work >>with a double import and then a little more work after merging the two >files >>together: >> >>PROC IMPORT OUT= WORK.INPUTa >> DATAFILE= "c:\DateTest.xls" >> DBMS=EXCEL REPLACE; >> SHEET="Sheet1$"; >> GETNAMES=YES; >> MIXED=NO; >> SCANTEXT=YES; >> USEDATE=YES; >> SCANTIME=YES; >>RUN; >> >>PROC IMPORT OUT= WORK.INPUTb >> DATAFILE= "c:\DateTest.xls" >> DBMS=EXCEL REPLACE; >> SHEET="Sheet1$"; >> GETNAMES=YES; >> MIXED=YES; >> SCANTEXT=YES; >> USEDATE=YES; >> SCANTIME=YES; >>RUN; >> >>data want (drop=bdate); >> merge INPUTa INPUTb (rename=(date=bdate)); >> if missing(date) then do; >> Date=inputn (bdate , 'anydtdte' , 20 ); >> if missing(Date) and substr(bdate,length(bdate)-2,1) eq '-' then do; >> if substr(bdate,length(bdate)-1) le 9 then bdate= >> catt(substr(bdate,1,length(bdate)-2),'20', >> substr(bdate,length(bdate)-1)); >> else bdate=catt(substr(bdate,1,length(bdate)-2),'19', >> substr(bdate,length(bdate)-1)); >> date=inputn (bdate , 'anydtdte' , 20 ); >> end; >> end; >>run; >> >>However, I wanted to include a solution for those who don't license >>SAS/ACCESS for pc formats as well. One of Koen Vverman's old excellent >SAS- >>L posts shows how one can accomplish getting and using functions in the >form >>of old-style Excel macros via DDE: >>http://www.listserv.uga.edu/cgi-bin/wa?A2=ind0209A&L=sas-l&P=12088 >> >>I was able to modify his example to accomplish what I wanted to do but, >>since my Excel macro skills aren't sufficiently strong, the solution is >>quite round about and requires a manual save of the end file. >> >>My code follows. What I am looking for is what to add to get the Excel >>macro to save the resulting file as a comma separated file. Of course, I >>wouldn't complain if anyone also had suggestions for cleaning up the code >>itself. >> >>Art >>-------- >>/*Set options and filename for dde commands*/ >>options noxsync noxwait xmin; >>filename sas2xl dde 'excel|system'; >> >>/*Open Excel*/ >>data _null_; >> length fid rc start stop time 8; >> fid=fopen('sas2xl','s'); >> if (fid le 0) then do; >> rc=system('start excel'); >> start=datetime(); >> stop=start+10; >> do while (fid le 0); >> fid=fopen('sas2xl','s'); >> time=datetime(); >> if (time ge stop) then fid=1; >> end; >> end; >> rc=fclose(fid); >>run; >> >>/*Open spreadsheet*/ >>data _null_; >> file sas2xl; >> put '[open("c:\datetest.xls")]'; >>run; >> >>/*Insert an old-style macro-sheet into the workbook.*/ >>data _null_; >> file sas2xl; >> put '[workbook.next()]'; >> put '[workbook.insert(3)]'; >>run; >> >>/*Create and run the macro*/ >>filename xlmacro dde 'excel|macro1!r1c1:r100c1' notab lrecl=200; >>data _null_; >> file xlmacro; >> put '=set.name("Tag",!$b$1)'; >> put '=formula("<>",Tag)'; >> put '=set.name("OldValue",!$c$1)'; >> put '=set.name("NewValue",!$b$2)'; >> put '=for.cell("CurrentCell",sheet1!$a$2:$a$100,true)'; >> put '=formula(get.cell(5,CurrentCell),OldValue)'; >> put '=formula("=concatenate(Tag,OldValue)",NewValue)'; >> put '=formula(NewValue,CurrentCell)'; >> put '=next()'; >> put '=halt(true)'; >> put '!dde_flush'; >> file sas2xl; >> put '[run("macro1!r1c1")]'; >>run; >>filename xlmacro clear; >> >>/*Save the spreadsheet as a csv file - then import the data*/ >>data want (keep=date); >> infile "c:\DateTest.csv" dsd dlm="," lrecl=32768 firstobs=2; >> informat rawdate $20.; >> input rawdate; >> format date date9.; >> rawdate=substr(rawdate,3); >> if anyalpha(rawdate) then do; >> date=inputn (rawdate , 'anydtdte' , 20 ); >> if missing(Date) and >> substr(rawdate,length(rawdate)-2,1) eq '-' then do; >> if substr(rawdate,length(rawdate)-1) le 9 then rawdate= >> catt(substr(rawdate,1,length(rawdate)-2),'20', >> substr(rawdate,length(rawdate)-1)); >> else rawdate= >> catt(substr(rawdate,1,length(rawdate)-2),'19', >> substr(rawdate,length(rawdate)-1)); >> date=inputn (rawdate , 'anydtdte' , 20 ); >> end; >> end; >> else Date=rawdate-21916; >>run;