Prev: ERROR message in the log - hard to understand
Next: adding +6 to age
From: Mark Sussman on 12 Mar 2010 13:20
Hi,

I'm trying to format the numbers in a database i'm trying to export to
excel using DDE since I have to do it 7,000 times and I've been
successful in formating some numbers but not those that need an
"accounting' format. Below is my successful code for the number
format with a comma separator and 2 decimal points and my unsuccessful
accounting format code:

successful:

put '[select("r3c20:r149c20")]';
put '[format.number("#,##0.00")]';

unsuccessful"

put '[select("r3c19:r149c19")]';
put '[format.number("_($* #,##0.00_);_($* (#,##0.00);_($* "-"??
_);_(@_)")]';
From: Arthur Tabachneck on 13 Mar 2010 00:20
Mark,

I'm not sure what you are trying to do. Doe the following come
close?:

filename ddecmds DDE "excel|system";
data _null_;
file ddecmds;
put '[select("r3c20:r149c20")]';
put '[format.number("#,##0.00")]';
put '[select("r3c19:r149c19")]';
put '[format.number("$#,##0.00")]';
run;
filename ddecmds clear;

HTH,
Art
----------------
On Mar 12, 1:20 pm, Mark Sussman <msuss...(a)gmail.com> wrote:
> Hi,
>
> I'm trying to format the numbers in a database i'm trying to export to
> excel using DDE since I have to do it 7,000 times and I've been
> successful in formating some numbers but not those that need an
> "accounting' format.  Below is my successful code for the number
> format with a comma separator and 2 decimal points and my unsuccessful
> accounting format code:
>
> successful:
>
> put '[select("r3c20:r149c20")]';
> put '[format.number("#,##0.00")]';
>
> unsuccessful"
>
> put '[select("r3c19:r149c19")]';
> put '[format.number("_($* #,##0.00_);_($* (#,##0.00);_($* "-"??
> _);_(@_)")]';

From: Mark Sussman on 13 Mar 2010 17:11

That pdf you sent had the answer

"One final note: Strictly speaking, the equivalent to the Excel
accounting format is
($* #,##0.00 ); ($* (#,##0.00); ($* "-"?? ); (@ )
rather than what is shown in the table (the difference being "-"??
rather than -??), but then that would result in the X4ML
command
[format.number(" ($* #,##0.00 ); ($* (#,##0.00); ($* "-"?? );
(@ )")],"

So my code was correct, I just had to remove the internal quotes.

Quick question do you know the DDE syntax for using the Excel sum
function? I'm trying to just do some vertical sums and I can't see to
get anything to work

Thanks

Mark

On Mar 13, 12:20 am, Arthur Tabachneck <art...(a)netscape.net> wrote:
> Mark,
>
> I'm not sure what you are trying to do.  Doe the following come
> close?:
>
> filename ddecmdsDDE"excel|system";
> data _null_;
>   file ddecmds;
>   put '[select("r3c20:r149c20")]';
>   put '[format.number("#,##0.00")]';
>   put '[select("r3c19:r149c19")]';
>   put '[format.number("$#,##0.00")]';
> run;
> filename ddecmds clear;
>
> HTH,
> Art
> ----------------
> On Mar 12, 1:20 pm, Mark Sussman <msuss...(a)gmail.com> wrote:
>
>
>
> > Hi,
>
> > I'm trying to format the numbers in a database i'm trying to export to
> > excel usingDDEsince I have to do it 7,000 times and I've been
> > successful in formating some numbers but not those that need an
> > "accounting' format.  Below is my successful code for the number
> > format with a comma separator and 2 decimal points and my unsuccessful
> > accounting format code:
>
> > successful:
>
> > put '[select("r3c20:r149c20")]';
> > put '[format.number("#,##0.00")]';
>
> > unsuccessful"
>
> > put '[select("r3c19:r149c19")]';
> > put '[format.number("_($* #,##0.00_);_($* (#,##0.00);_($* "-"??
> > _);_(@_)")]';

From: Mark Sussman on 13 Mar 2010 17:38

Below is my final code for the accounting format in Excel using DDE

put '[select("r3c33:r151c33")]';
put '[format.number("_($* #,##0.00_);_($* (#,##0.00);_($* -??
_);_(@_)")]';

Thanks again

Mark

On Mar 13, 5:11 pm, Mark Sussman <msuss...(a)gmail.com> wrote:
> That pdf you sent had the answer
>
> "One final note: Strictly speaking, the equivalent to the Excel
> accounting format is
> ($* #,##0.00 ); ($* (#,##0.00); ($* "-"?? ); (@ )
> rather than what is shown in the table (the difference being "-"??
> rather than -??), but then that would result in the X4ML
> command
> [format.number(" ($* #,##0.00 ); ($* (#,##0.00); ($* "-"?? );
> (@ )")],"
>
> So my code was correct, I just had to remove the internal quotes.
>
> Quick question do you know the DDE syntax for using the Excel sum
> function?  I'm trying to just do some vertical sums and I can't see to
> get anything to work
>
> Thanks
>
> Mark
>
> On Mar 13, 12:20 am, Arthur Tabachneck <art...(a)netscape.net> wrote:
>
>
>
> > Mark,
>
> > I'm not sure what you are trying to do.  Doe the following come
> > close?:
>
> > filename ddecmdsDDE"excel|system";
> > data _null_;
> >   file ddecmds;
> >   put '[select("r3c20:r149c20")]';
> >   put '[format.number("#,##0.00")]';
> >   put '[select("r3c19:r149c19")]';
> >   put '[format.number("$#,##0.00")]';
> > run;
> > filename ddecmds clear;
>
> > HTH,
> > Art
> > ----------------
> > On Mar 12, 1:20 pm, Mark Sussman <msuss...(a)gmail.com> wrote:
>
> > > Hi,
>
> > > I'm trying to format the numbers in a database i'm trying to export to
> > > excel usingDDEsince I have to do it 7,000 times and I've been
> > > successful in formating some numbers but not those that need an
> > > "accounting' format.  Below is my successful code for the number
> > > format with a comma separator and 2 decimal points and my unsuccessful
> > > accounting format code:
>
> > > successful:
>
> > > put '[select("r3c20:r149c20")]';
> > > put '[format.number("#,##0.00")]';
>
> > > unsuccessful"
>
> > > put '[select("r3c19:r149c19")]';
> > > put '[format.number("_($* #,##0.00_);_($* (#,##0.00);_($* "-"??
> > > _);_(@_)")]';

 | 
Pages: 1
Prev: ERROR message in the log - hard to understand
Next: adding +6 to age