Hi Art:

I know this might be a little late but from what I can tell, you didn't receive an answer on how to save an excel file as CSV using DDE. I keep a copy of the EXCEL function reference handy and if you use the SAVE.AS function like so

data _null_;
file sas2xl;
put  '[Save.as("C:\Path\file.csv",X)]';
run;

where X is either 6, 22, 23, or 24.

The Excel function reference Describes these numbers as CSV, CSV(Macintosh), CSV (WIndows), & CSV(MS-DOS) respectively. I have never used DDE to save as anyhthing other than an excel file (X=1) so I cannot comment on the success of your results.

Regards,

Kevin

On Sun, Nov 29, 2009 at 12:30 PM, 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;
>



--
==============================
WenSui Liu
Blog   : statcompute.spaces.live.com
Tough Times Never Last. But Tough People Do.  - Robert Schuller ==============================






_________________________________________________________________