BookmarkSubscribeRSS Feed
RLC
Calcite | Level 5 RLC
Calcite | Level 5
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)
11 REPLIES 11
sbb
Lapis Lazuli | Level 10 sbb
Lapis Lazuli | Level 10
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
RLC
Calcite | Level 5 RLC
Calcite | Level 5
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
sbb
Lapis Lazuli | Level 10 sbb
Lapis Lazuli | Level 10
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.
RLC
Calcite | Level 5 RLC
Calcite | Level 5
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
sbb
Lapis Lazuli | Level 10 sbb
Lapis Lazuli | Level 10
As previously suggested, share your code for serious consideration.

Scott Barry
SBBWorks, Inc.
RLC
Calcite | Level 5 RLC
Calcite | Level 5
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;
data_null__
Jade | Level 19
> (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]
RLC
Calcite | Level 5 RLC
Calcite | Level 5
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
RLC
Calcite | Level 5 RLC
Calcite | Level 5
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;
data_null__
Jade | Level 19
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.
sbb
Lapis Lazuli | Level 10 sbb
Lapis Lazuli | Level 10
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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

Discussion stats
  • 11 replies
  • 1206 views
  • 0 likes
  • 3 in conversation