Anyone familiar with Excel 4 macro language?
in [SAS]
|
From: Wensui Liu on 29 Nov 2009 13:25 Art excel 4 macro language is seldom used. however, sas programmers still have to use it in dde. you can find the manual / help file on microsoft website. On Sun, Nov 29, 2009 at 12:30 PM, Arthur Tabachneck <art297(a)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 ==============================
From: S=?ISO-8859-1?Q?=C3=B8ren?= Lassen on 30 Nov 2009 01:54 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(a)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;
From: Arthur Tabachneck on 30 Nov 2009 07:58 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(a)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(a)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;
|
Pages: 1 Prev: SAS/toolkit Next: How to create self join in SAS DI Studio using JOIN Transformation?? |