Date: Thu, 13 Nov 2008 12:49:40 -0600
Reply-To: "./ ADD NAME=Data _null_," <iebupdte@GMAIL.COM>
Sender: "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From: "./ ADD NAME=Data _null_," <iebupdte@GMAIL.COM>
Subject: Re: Another Excel Libname Question
In-Reply-To: <ce1fb7450811130628qa110321lcfcdd1eb66ec3515@mail.gmail.com>
Content-Type: text/plain; charset=ISO-8859-1
I went ahead and made this into a macro like thing. I used the macro
argument feature that allows positional parameters to be used like
keyword parameters.
You may or may not find it useful.
libname STORPGM (work); *Where you actually store your stored data
step programs;
data _null_ / pgm=STORPGM.SetGuessingRows;
attrib set length=$32;
attrib GuessRows length=8;
set = upcase(symget('SET'));
if set eq 'RESET' then do;
if not symexist('CurrentGuessRows') then do;
putlog 'ERR' 'OR: Global macro variable "CurrentGuessRows"
not defined cannot reset Guessing Rows';
stop;
end;
GuessRows = input(symget('currentGuessRows'),f16.);
end;
else GuessRows = input(resolve('%sysevalF(&set,Integer)'),f16.);
if missing(guessRows) or sign(guessRows) lt 0 then do;
putlog 'WARNING: Bad ' GuessRows=;
stop;
end;
putlog 'NOTE: Setting MicroSoft Jet Engine Guessing Rows=' GuessRows;
length script command filevar $256;
script = catx('\',pathname('WORK'),'GuessRows.vbs');
filevar = script;
file dummy1 filevar=filevar recfm=v lrecl=512;
put 'Set objShell = WScript.CreateObject("WScript.Shell")';
put 'const target =
"HKLM\SOFTWARE\Microsoft\Jet\4.0\Engines\Excel\TypeGuessRows"';
put 'With objShell';
put ' GuessRows = .RegRead (target)';
put ' WScript.Echo "Current Value of GuessRows=" & GuessRows';
put ' .RegWrite target,' guessRows ', "REG_DWORD"';
put ' GuessRows = .RegRead (target)';
put ' WScript.Echo "GuessRows Set to GuessRows=" & GuessRows';
put ' End with';
filevar = catx('\',pathname('WORK'),'dummy.vbs');
file dummy1 filevar=filevar;
filevar = script;
infile dummy2 filevar=filevar end=eof;
putlog 'NOTE: The SCRIPT';
do _n_ = 1 by 1 until(eof);
input;
putlog 'NOTE- ' _n_ z3. +2 _infile_;
end;
command = catx(' ','cscript',quote(trim(script)));
infile dummy pipe filevar=command lrecl=1024 end=eof;
putlog 'NOTE: The SCRIPT output';
do _n_ = 1 by 1 until(eof);
input @;
if index(_infile_,'Current Value of GuessRows')
then input @'GuessRows=' CurrentGuessRows;
else input;
putlog 'NOTE- ' _n_ z3. +2 _infile_;
end;
if not symexist('CurrentGuessRows')
then call symputX('CurrentGuessRows',CurrentGuessRows,'G');
else call symdel('CurrentGuessRows');
stop;
run;
%macro GuessRows(set);
data pgm=STORPGM.SetGuessingRows;
run;
%put NOTE: Macro &sysmacroname ending execution;
%mend GuessRows;
%GuessRows(reset); /*error no value to reset */
%GuessRows(set=fffx);
%GuessRows(reset);
On 11/13/08, ./ ADD NAME=Data _null_, <iebupdte@gmail.com> wrote:
> On 11/13/08, Ed Heaton <EdHeaton@westat.com> wrote:
> > Wow. Thanks, Mr. _null_.
> >
> > I am scheduled to give a talk on using the LibName statement to access Excel files this coming Monday at Wachovia in Charlotte, NC. Can I use your code below? If yes, how do I cite it so that the author gets proper credit?
>
> You don't need to mention me specifically, but if you want to my
> "screen name" will suffice. I am looking for the URL to the
> "Scripting Guys" article where I got the important part, I think you
> should reference that. I will pass that along if I find it. You can
> find all kinds of cool scripts at their web site. All the VBSCRIPT
> stuff I have posted here come from them.
>
> >
> > One change that I would recommend, however. Jet 4.0 (the engine) believes that Excel has a maximum of 16K rows, but newer versions of Excel allow 64K rows. So, I now use and recommend changing the value to TypeGuessRows=FFFF.
>
> %let guessRows = ffffx; will work to set the value to 65535, in the
> current version of the program.
>
> I think the program should work as a set/reset macro call or something
> like that. Perhaps parameterized something like...
>
> %GuessingRows(set=ffffx);
> <do stuff>
> %GuessingRows(reset);
>
> >
> > Ed
> >
> > Edward Heaton, Senior Systems Analyst,
> > Westat (An Employee-Owned Research Corporation),
> > 1650 Research Boulevard, TB-286, Rockville, MD 20850-3195
> > Voice: (301) 610-4818 Fax: (301) 294-2085
> > mailto:EdHeaton@Westat.com http://www.Westat.com
> >
> >
> >
> >
> > -----Original Message-----
> > From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU] On Behalf Of ./ ADD NAME=Data _null_,
> > Sent: Wednesday, November 12, 2008 6:19 PM
> > To: SAS-L@LISTSERV.UGA.EDU
> > Subject: Re: Another Excel Libname Question
> >
> >
> > I think this code is safe. I got the script part from the MS "Scripting Guys".
> >
> > data _null_ / pgm=work.SetGuessingRows;
> > length script command filevar $256;
> > script = catx('\',pathname('WORK'),'GuessRows.vbs');
> > filevar = script;
> > GuessRows = input(resolve('%sysevalF(&guessRows,Integer)'),f16.);
> > putlog 'NOTE: ' GuessRows=;
> > if missing(guessRows) or sign(guessRows) lt 0 then do;
> > putlog 'WARNING: Bad ' GuessRows=;
> > stop;
> > end;
> > file dummy1 filevar=filevar recfm=v lrecl=512;
> > put 'Set objShell = WScript.CreateObject("WScript.Shell")';
> > put 'const target = "HKLM\SOFTWARE\Microsoft\Jet\4.0\Engines\Excel\TypeGuessRows"';
> > put 'With objShell';
> > put ' GuessRows = .RegRead (target)';
> > put ' WScript.Echo "Current Value of GuessRows=" & GuessRows';
> > put ' .RegWrite target,' guessRows ', "REG_DWORD"';
> > put ' GuessRows = .RegRead (target)';
> > put ' WScript.Echo "GuessRows Set to GuessRows=" & GuessRows';
> > put ' End with';
> >
> > filevar = catx('\',pathname('WORK'),'dummy.vbs');
> > file dummy1 filevar=filevar;
> > filevar = script;
> > infile dummy2 filevar=filevar end=eof;
> > putlog 'NOTE: The SCRIPT';
> > do _n_ = 1 by 1 until(eof);
> > input;
> > putlog 'NOTE- ' _n_ z3. +2 _infile_;
> > end;
> > command = catx(' ','cscript',quote(trim(script)));
> > infile dummy pipe filevar=command lrecl=1024 end=eof;
> > putlog 'NOTE: The SCRIPT output';
> > do _n_ = 1 by 1 until(eof);
> > input;
> > putlog 'NOTE- ' _n_ z3. +2 _infile_;
> > end;
> > stop;
> > run;
> >
> > %let guessRows = 0;
> > data pgm=work.SetGuessingRows;
> > run;
> >
> > %let guessRows = 2**3;
> > data pgm=work.SetGuessingRows;
> > run;
> >
> >
> > This needs a way to save the current value and the reset it when done. We can work on that.
> >
> > On 11/12/08, Ken Barz <Ken.Barz@cpcmed.org> wrote:
> > > A paper that I've read talks about using mixed=yes with the Excel
> > > libname engine. It then talks about setting the MS Jet TypeGuessRows
> > > key in the Windows registry to 0 so that the whole column will be
> > > looked at to determine the field type (as opposed to the first 8
> > > rows.)
> > >
> > >
> > >
> > > So, as I don't want to be running around checking (and re-checking)
> > > everyone's Windows registry, is there a way to (safely) change this
> > > key in SAS code?
> > >
> > > Thanks
> > >
> >
>
| 
