topic Re: DDE Excel internal value in Microsoft Integration with SAS

DDE Excel internal value

RLC — Sun, 06 Dec 2009 17:30:23 GMT

Hi,
In Excel, I have data in cell as "123.12" or "123" This a formatted value for internal value "123.123456789" and "123"

By DDE, I would read this kind of data without modifying the formatted value in order to have internal value in SAS (with all digit after the period)

If i have only decimal value, a informat as best16.8 is sufficient
If I have integer value as "123" I obtain false result as "0.00000123"

Please, who knows how to do this ?
(without modifying formatted value in Excel)

Re: DDE Excel internal value

sbb — Mon, 07 Dec 2009 00:43:13 GMT

Given what you have explained, you should not need to use BEST16.8 at all with a decimal field/value.

Scott Barry
SBBWorks, Inc.

data _null_;
x = input('123.1212',16.);
y = input('123',16.);
putlog _all_;
run;

Suggested Google advanced search argument:

input decimal number informat site:sas.com

Re: DDE Excel internal value

RLC — Mon, 07 Dec 2009 08:22:59 GMT

Thanks sbb, you don't understand my question

Please reread this

In Excel the formatted value (in the screen) is "0.12"
In Excel the internal value is 0.123456

In SAS the internal value is 0.12 whatever the informat use to read data

In SAS I would like 0.123456 without modifying the Excel sheet

I have different value in the same column as
Excel internal value
12
12.12
0.12345
Excel formatted (screen) values
12
12.12
0.12

Roger

Re: DDE Excel internal value

sbb — Mon, 07 Dec 2009 15:58:19 GMT

Suggest you share whatever SAS code you have tried to attempt to import your Excel data using DDE. My point was specific to the use of an INFORMAT to control how SAS reads a data field, not specific to Excel in particular. I would expect that when you specify an INFORMAT, you will see expected data conversion / input.

Suggested Google advanced search argument for SAS support website:

dde import excel read internal cell value site:sas.com

Scott Barry
SBBWorks, Inc.

Re: DDE Excel internal value

RLC — Sun, 13 Dec 2009 14:44:26 GMT

euh...
Thank for the training about Google...

I preferred a serious answer

It seem that nobody knows if it's possible (Ihave a way by xml file but it is not appicable for my type of Excel sheet)

I wait for SAS answer

Re: DDE Excel internal value

sbb — Sun, 13 Dec 2009 18:36:33 GMT

As previously suggested, share your code for serious consideration.

Scott Barry
SBBWorks, Inc.

Re: DDE Excel internal value

data_null__ — Mon, 14 Dec 2009 13:00:21 GMT

> (without modifying formatted value in Excel)

I believe the only way to achieve this using DDE is to CLEAR the formats before reading. Which can be easliy accomplished using DDE.

Example input all values are actually 123.12345678.

[pre]
123.1 123.1 123.1234568 $123.12
123.1 123.1 123.1234568 $123.12
123.1 123.1 123.1234568 $123.12
123.1 123.1 123.1234568 $123.12
123.1 123.1 123.1234568 $123.12
123.1 123.1 123.1234568 $123.12
[/pre]

A little DDE to read it.

[pre]
data RLC;
length ddetext $128;
ddetext = 'excel|system';

file dummy1 dde filevar=ddetext;
put '[SELECT.SPECIAL(5)]';
put '[CLEAR(2)]';

ddeText = 'excel|system!Selection';
infile dummy2 dde notab dlm='09'x dsd filevar=ddeText lrecl=256;
input selection:$128.;
putlog selection;

ddeText = cats('EXCEL|',selection);
infile dummy3 dde notab dlm='09'x dsd end=eof filevar=ddeText lrecl=256 missover;
do while(not eof);
input (f1-f4)(:f16.);
output;
end;
stop;
run;
proc print;
format _numeric_ F16.8;
run;
[/pre]

Example result

[pre]
The SAS System 06:27 Monday, December 14, 2009 18

Obs selection f1 f2 f3 f4

1 [BookFormated1.xls]Sheet1!R1C1:R17C4 123.12345680 123.12345680 123.12345680 123.12345680
2 [BookFormated1.xls]Sheet1!R1C1:R17C4 123.12345680 123.12345680 123.12345680 123.12345680
3 [BookFormated1.xls]Sheet1!R1C1:R17C4 123.12345680 123.12345680 123.12345680 123.12345680
4 [BookFormated1.xls]Sheet1!R1C1:R17C4 123.12345680 123.12345680 123.12345680 123.12345680
5 [BookFormated1.xls]Sheet1!R1C1:R17C4 123.12345680 123.12345680 123.12345680 123.12345680
6 [BookFormated1.xls]Sheet1!R1C1:R17C4 123.12345680 123.12345680 123.12345680 123.12345680
7 [BookFormated1.xls]Sheet1!R1C1:R17C4 123.12345680 123.12345680 123.12345680 123.12345680
8 [BookFormated1.xls]Sheet1!R1C1:R17C4 123.12345680 123.12345680 123.12345680 123.12345680
9 [BookFormated1.xls]Sheet1!R1C1:R17C4 123.12345680 123.12345680 123.12345680 123.12345680
10 [BookFormated1.xls]Sheet1!R1C1:R17C4 123.12345680 123.12345680 123.12345680 123.12345680
11 [BookFormated1.xls]Sheet1!R1C1:R17C4 123.12345680 123.12345680 123.12345680 123.12345680
12 [BookFormated1.xls]Sheet1!R1C1:R17C4 123.12345680 123.12345680 123.12345680 123.12345680
13 [BookFormated1.xls]Sheet1!R1C1:R17C4 123.12345680 123.12345680 123.12345680 123.12345680
14 [BookFormated1.xls]Sheet1!R1C1:R17C4 123.12345680 123.12345680 123.12345680 123.12345680
15 [BookFormated1.xls]Sheet1!R1C1:R17C4 123.12345680 123.12345680 123.12345680 123.12345680
16 [BookFormated1.xls]Sheet1!R1C1:R17C4 123.12345680 123.12345680 123.12345680 123.12345680
17 [BookFormated1.xls]Sheet1!R1C1:R17C4 123.12345680 123.12345680 123.12345680 123.12345680
[/pre]

Re: DDE Excel internal value

RLC — Mon, 14 Dec 2009 13:55:50 GMT

SBB, It's not a question for beginers or end SAS user

filename ALIRE dde "Excel|DDE01!L2C1:L100C100" ;
data __p4;
infile ALIRE dlm='09'x notab dsd missover LRECL=1000000;
input N V1-V8;
run;
proc print data=__p4;
format v1-v8 16.8;
run;

Re: DDE Excel internal value

RLC — Mon, 14 Dec 2009 13:59:54 GMT

Thank Data_null_
at the first reading I don't understand all the statements - I read in detail during the next weekend and inform you about the results.
Thank for your participation

Re: DDE Excel internal value

RLC — Mon, 14 Dec 2009 14:04:07 GMT

This a quasi compléte exemple of my code

I have more than 100 Excel file with many sheets by workbook to read

*--------------------------------------------------------------------------;
options noxwait noxsync;
%let EXCELexe = %str(C:\Program Files\Microsoft Office\Office11\EXCEL.EXE);
filename EXCELcmd dde "EXCEL|SYSTEM";
%let EXCELdat = %nrbquote(&RACINE\DDE);
%let CLASSEUR = %nrbquote(DDE01.xls);
data _null_;
command='"' || "&EXCELexe" || '"';
call system(command);
run;
data _null_;
x=sleep(5);
run;
data _null_;
file EXCELcmd;
length command $ 200;
command='[app.minimize()]';
put command;
run;
data _null_;
file EXCELcmd;
length command $ 200;
command='[open("' || "&EXCELdat\&CLASSEUR" || '")]';
put command;
run;

filename ALIRE dde "Excel|DDE01!L2C1:L100C100" ;

data __p4;
infile ALIRE dlm='09'x notab dsd missover LRECL=1000000;
input N V1-V8;
run;
proc print data=__p4;
format v1-v8 16.8;
run;

data _null_;
file EXCELcmd;
put '[error(false)]';
put '[quit()]';
run;

Re: DDE Excel internal value

sbb — Mon, 14 Dec 2009 14:18:30 GMT

Based on the code example you posted, suggest you use an INFORMAT statement to control SAS' read-up of your V1-V8 columns, rather than taking the SAS default -- also, similar to what has been posted with an INPUT statement and having an explicit INFORMAT coded.

Scott Barry
SBBWorks, Inc.

Re: DDE Excel internal value

data_null__ — Mon, 14 Dec 2009 15:35:13 GMT

This code is not for beginner or end users. You can consult the documentation to learn the details of the SAS code. You will need the MACROFUN documentation from MicroSoft to learn details about SELECT.SPECIAL and CLEAR. I'm not sure where the details of EXEC|SYSTEM are documented.

The code I posted assumes the XL file is open, which I see you are able to do from looking at the code you posted.

No need to report the results, I know what the program does.