Date: Mon, 29 Dec 2008 19:52:48 -0500
Reply-To: Joe Whitehurst <joewhitehurst@GMAIL.COM>
Sender: "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From: Joe Whitehurst <joewhitehurst@GMAIL.COM>
Subject: Re: read excel through dde
In-Reply-To: <200812292139.mBTBkSiu008832@malibu.cc.uga.edu>
Content-Type: text/plain; charset=ISO-8859-1
Here is a more comprehensive DDE example:
length tmp1 tmp2 msg path path1 path2 path3 sas_path sheet_name
work_book topics datafile $200
sas_dataset_name $32 libref sas_libref $8 cntrl_char $1 sheet
$30;
Init:
rc=libname('sasdata1',"C:\WebFirst\download\AA\tennessee\sasdata1");
excel_names_dsid=open('sasdata1.tnaa_excel_names','i');
call set(excel_names_dsid);
rc=filename('cmds','Excel|System','DDE');
rc=filename('topics','Excel|System|topics','DDE');
*put rc=;
*msg=sysmsg();
put msg=;
cmds_fid=fopen('cmds','S');
do while(fetch(excel_names_dsid)=0);
rc=libname(sas_libref,sas_path);
path1=path;
path=quote(path);
rc=fput(cmds_fid,'[OPEN('||path||')]');
*put rc=;
*put cmds_fid=;
*msg=sysmsg();
*put msg=;
rc=fwrite(cmds_fid);
*put rc=;
*put cmds_fid=;
*msg=sysmsg();
*put msg=;
* call wait(5);
submit continue;
filename topics dde 'excel|system!topics' lrecl=5000;
data topics worksheet_A1B;
length topics $5000;
infile topics pad dsd notab dlm='09'x;
input topics $ @@;
if index(upcase(topics),'A1A)') |
index(upcase(topics),'A1B)') |
index(upcase(topics),'A2)') |
index(upcase(topics),'A3A)') |
index(upcase(topics),'A3B)') |
index(upcase(topics),'A3C)') |
index(upcase(topics),'A3D)') |
index(upcase(topics),'A3E)') |
index(upcase(topics),'A3F)') |
index(upcase(topics),'A4A)') |
index(upcase(topics),'A4B)') |
index(upcase(topics),'A4C)') |
index(upcase(topics),'A4D)') |
index(upcase(topics),'A4E)') |
index(upcase(topics),'A4F)') |
index(upcase(topics),'A5)') |
index(upcase(topics),'A6)') |
index(upcase(topics),'A7)') |
index(upcase(topics),'A8)') |
index(upcase(topics),'A9)') |
index(upcase(topics),'A10)') |
index(upcase(topics),'A11)');
if index(upcase(topics),'A1A)') then sheet_order=1;
else if index(upcase(topics),'A1B)') then sheet_order=2;
else if index(upcase(topics),'A2)') then sheet_order=3;
else if index(upcase(topics),'A3A)') then sheet_order=4;
else if index(upcase(topics),'A3B)') then sheet_order=5;
else if index(upcase(topics),'A3C)') then sheet_order=6;
else if index(upcase(topics),'A3D)') then sheet_order=7;
else if index(upcase(topics),'A3E)') then sheet_order=8;
else if index(upcase(topics),'A3F)') then sheet_order=9;
else if index(upcase(topics),'A4A)') then sheet_order=10;
else if index(upcase(topics),'A4B)') then sheet_order=11;
else if index(upcase(topics),'A4C)') then sheet_order=12;
else if index(upcase(topics),'A4D)') then sheet_order=13;
else if index(upcase(topics),'A4E)') then sheet_order=14;
else if index(upcase(topics),'A4F)') then sheet_order=15;
else if index(upcase(topics),'A5)') then sheet_order=16;
else if index(upcase(topics),'A6)') then sheet_order=17;
else if index(upcase(topics),'A7)') then sheet_order=18;
else if index(upcase(topics),'A8)') then sheet_order=19;
else if index(upcase(topics),'A9)') then sheet_order=20;
else if index(upcase(topics),'A10)') then sheet_order=21;
else if index(upcase(topics),'A11)') then sheet_order=22;
if index(upcase(topics),'A1B)') then output worksheet_A1B;
output topics;
run;
proc sort data=topics;
by sheet_order;
run;
proc append base=sasuser.tennessee_AA_worksheet_A1B
data=worksheet_a1B;
run;
proc append base=sasuser.tennessee_AA_topics data=topics;
run;
endsubmit;
topics_dsid=open('work.topics','i');
call set(topics_dsid);
number_of_topics=attrn(topics_dsid,'nobs');
do i=1 to number_of_topics;
rc=fetchobs(topics_dsid,i);
sheet_start_position=index(upcase(topics),']');
sheet=trim(left(substr(topics,sheet_start_position+1)||' $'));
right_paren_position=index(sheet,')');
put sheet=;
put right_paren_position=;
if right_paren_position=3 then
sas_dataset_name=put(upcase(substr(sheet,1,2)),$fxspell32.);
else if right_paren_position=4 then
sas_dataset_name=put(upcase(substr(sheet,1,3)),$fxspell32.);
if index(upcase(topics),'A1B') then link
deal_with_stupid_yokel_bullshit;
*else if index(upcase(topics),'A4D') then link
deal_with_idiot_yokel_bullshit;
else do;
path3=trim(left('excel|'||path2||trim(left(topics))||'!'||'r1c1:r30c9'));
*put path3=;
* call wait(5);
submit continue;
%let observations=;
filename readxcel dde "&path3";
data &sas_libref.&sas_dataset_name(drop=observations);
infile readxcel dlm='09'x notab dsd missover;
informat f1 $40. f2-f9 $8.;
input f1-f9;
if index(upcase(F1),' MEN') |
index(upcase(F1),' WOMEN');
observations+1;
if observations<19;
call symput('observations',trim(left(observations)));
run;
data sas_dataset_name;
length sas_dataset_name $50 Excel_Workbook_And_Worksheet $150;
sas_dataset_name="&sas_libref.&sas_dataset_name";
if index(upcase(sas_dataset_name),'A1A') then j=1;
else if index(upcase(sas_dataset_name),'A1B') then j=2;
else if index(upcase(sas_dataset_name),'A2') then j=3;
else if index(upcase(sas_dataset_name),'A3A') then j=4;
else if index(upcase(sas_dataset_name),'A3B') then j=5;
else if index(upcase(sas_dataset_name),'A3C') then j=6;
else if index(upcase(sas_dataset_name),'A3D') then j=7;
else if index(upcase(sas_dataset_name),'A3E') then j=8;
else if index(upcase(sas_dataset_name),'A3F') then j=9;
else if index(upcase(sas_dataset_name),'A4A') then j=10;
else if index(upcase(sas_dataset_name),'A4B') then j=11;
else if index(upcase(sas_dataset_name),'A4C') then j=12;
else if index(upcase(sas_dataset_name),'A4D') then j=13;
else if index(upcase(sas_dataset_name),'A4E') then j=14;
else if index(upcase(sas_dataset_name),'A4F') then j=15;
else if index(upcase(sas_dataset_name),'A5') then j=16;
else if index(upcase(sas_dataset_name),'A6') then j=17;
else if index(upcase(sas_dataset_name),'A7') then j=18;
else if index(upcase(sas_dataset_name),'A8') then j=19;
else if index(upcase(sas_dataset_name),'A9') then j=20;
else if index(upcase(sas_dataset_name),'A10') then j=21;
else if index(upcase(sas_dataset_name),'A11') then j=22;
observations=input(trim(left("&observations")),2.);
excel_workbook_and_worksheet="&path3";
run;
proc append base=sasuser.tennessee_AA_sas_dataset_name
data=sas_dataset_name force; run;
endsubmit;
filenamerc=filename('readxcel','','DDE');
end;
end;
rc=fput(cmds_fid,'[CLOSE('||path||')]');
closerc=close(topics_dsid);
rc=delete('topics');
call wait(5);
end;
return;
deal_with_stupid_yokel_bullshit:
sheet_start_position=index(upcase(topics),']');
sheet=trim(left(substr(topics,sheet_start_position+1)||' $'));
datafile="C:\WebFirst\download\AA\tennessee\Excel\"||compress(substr(topics,1,sheet_start_position-1),'[');
rc=fput(cmds_fid,'[CLOSE('||path||')]');
*put rc=;
*put cmds_fid=;
*msg=sysmsg();
*put msg=;
rc=fwrite(cmds_fid);
call wait(3);
put ' ';
put ' ';
put ' ';
put ' ';
put ' ';
put '!!!!!!!!!!!!!!!!!!!!!!!!!!';
put datafile=;
put sheet=;
put SAS_LIBREF=;
put sas_dataset_name=;
put '!!!!!!!!!!!!!!!!!!!!!!!!!!';
put ' ';
put ' ';
put ' ';
put ' ';
put ' ';
put ' ';
submit continue;
proc import out=&sas_libref.&sas_dataset_name
datafile="&datafile"
dbms=excel replace;
sheet="&sheet";
getnames=yes;
mixed=yes;
scantext=yes;
usedate=yes;
scantime=yes;
run;
data sas_dataset_name;
length sas_dataset_name $50 excel_workbook_and_worksheet $150;
sas_dataset_name="&sas_libref.&sas_dataset_name";
if index(upcase(sas_dataset_name),'A1A') then j=1;
else if index(upcase(sas_dataset_name),'A1B') then j=2;
else if index(upcase(sas_dataset_name),'A2') then j=3;
else if index(upcase(sas_dataset_name),'A3A') then j=4;
else if index(upcase(sas_dataset_name),'A3B') then j=5;
else if index(upcase(sas_dataset_name),'A3C') then j=6;
else if index(upcase(sas_dataset_name),'A3D') then j=7;
else if index(upcase(sas_dataset_name),'A3E') then j=8;
else if index(upcase(sas_dataset_name),'A3F') then j=9;
else if index(upcase(sas_dataset_name),'A4A') then j=10;
else if index(upcase(sas_dataset_name),'A4B') then j=11;
else if index(upcase(sas_dataset_name),'A4C') then j=12;
else if index(upcase(sas_dataset_name),'A4D') then j=13;
else if index(upcase(sas_dataset_name),'A4E') then j=14;
else if index(upcase(sas_dataset_name),'A4F') then j=15;
else if index(upcase(sas_dataset_name),'A5') then j=16;
else if index(upcase(sas_dataset_name),'A6') then j=17;
else if index(upcase(sas_dataset_name),'A7') then j=18;
else if index(upcase(sas_dataset_name),'A8') then j=19;
else if index(upcase(sas_dataset_name),'A9') then j=20;
else if index(upcase(sas_dataset_name),'A10') then j=21;
else if index(upcase(sas_dataset_name),'A11') then j=22;
excel_workbook_and_worksheet="&datafile"||"&sheet";
run;
proc append base=sasuser.tennessee_AA_sas_dataset_name
data=sas_dataset_name force; run;
endsubmit;
sas_dataset_name=_blank_;
rc=fput(cmds_fid,'[OPEN('||path||')]');
*put rc=;
*put cmds_fid=;
*msg=sysmsg();
*put msg=;
rc=fwrite(cmds_fid);
return;
deal_with_idiot_yokel_bullshit:
sheet_start_position=index(upcase(topics),']');
sheet=trim(left(substr(topics,sheet_start_position+1)||' $'));
datafile="C:\WebFirst\download\AA\tennessee\Excel\"||compress(substr(topics,1,sheet_start_position-1),'[');
put datafile=;
put sheet=;
put SAS_LIBREF=;
PUT sas_dataset_name=;
submit continue;
/*
proc import out=&sas_libref.&sas_dataset_name
datafile="&datafile"
dbms=excel replace;
sheet="&sheet";
getnames=yes;
mixed=yes;
scantext=yes;
usedate=yes;
scantime=yes;
run;
*/
data &sas_libref.&sas_dataset_name(drop=observaions);
length F1 $40 F2-F6 $8;
do observations=1 to 17;
F1='KEEP IDIOT YOKEL BULLSHIT';
output &sas_libref.&sas_dataset_name;
end;
run;
data sas_dataset_name;
length sas_dataset_name $50 excel_workbook_and_worksheet $150;
sas_dataset_name="&sas_libref.&sas_dataset_name";
if index(upcase(sas_dataset_name),'A1A') then j=1;
else if index(upcase(sas_dataset_name),'A1B') then j=2;
else if index(upcase(sas_dataset_name),'A2') then j=3;
else if index(upcase(sas_dataset_name),'A3A') then j=4;
else if index(upcase(sas_dataset_name),'A3B') then j=5;
else if index(upcase(sas_dataset_name),'A3C') then j=6;
else if index(upcase(sas_dataset_name),'A3D') then j=7;
else if index(upcase(sas_dataset_name),'A3E') then j=8;
else if index(upcase(sas_dataset_name),'A3F') then j=9;
else if index(upcase(sas_dataset_name),'A4A') then j=10;
else if index(upcase(sas_dataset_name),'A4B') then j=11;
else if index(upcase(sas_dataset_name),'A4C') then j=12;
else if index(upcase(sas_dataset_name),'A4D') then j=13;
else if index(upcase(sas_dataset_name),'A4E') then j=14;
else if index(upcase(sas_dataset_name),'A4F') then j=15;
else if index(upcase(sas_dataset_name),'A5') then j=16;
else if index(upcase(sas_dataset_name),'A6') then j=17;
else if index(upcase(sas_dataset_name),'A7') then j=18;
else if index(upcase(sas_dataset_name),'A8') then j=19;
else if index(upcase(sas_dataset_name),'A9') then j=20;
else if index(upcase(sas_dataset_name),'A10') then j=21;
else if index(upcase(sas_dataset_name),'A11') then j=22;
excel_workbook_and_worksheet="&datafile"||"&sheet";
run;
proc append base=sasuser.tennessee_AA_sas_dataset_name
data=sas_dataset_name force; run;
endsubmit;
sas_dataset_name=_blank_;
return;
On 12/29/08, Arthur Tabachneck <art297@netscape.net> wrote:
> Jeff,
>
> Your code only tries to read 2 columns in 5 rows. That is likely the
> source of your current problem, as you indicate that you are trying to
> read 3 columns.
>
> Art
> --------
>
> On Mon, 29 Dec 2008 15:43:39 -0500, Jeff <zhujp98@GMAIL.COM> wrote:
>
> > id type label 98 Binary The occurrence of a "HUMAN IMMUNODEFICIENCY
> >VIRUS" claim during modeling year 167 Binary The occurrence of
> a "METABOLIC
> >COMPLICATIONS" claim due to diabetes during the modeling year
> >I have above excel file with 3 columns. I want ot read it to sas throught
> >dde. (We do not have import sas modules)
> >I used following code:
> >**
> >
> >options noxwait noxsync;
> >
> >x *'"c:\program files\microsoft office\office10\excel.exe"'*;
> >
> >data _null_;
> >
> >x=sleep(5);
> >
> >run;
> >
> >*;
> >
> >/* open Excel workbook */
> >
> >/*"N:\PM\PM_RF\CSVs\definitionT.csv" */
> >
> >*;
> >
> >filename ddecmd dde *'excel|system'*;
> >
> >data _null_;
> >
> >file ddecmd;
> >
> >put *'[FILE-OPEN("N:\PM\PM_RF\CSVs\definitionT.xls")]'*;
> >
> >run;
> >
> >*;
> >
> >/* specify desired Excel worksheet cell range */
> >
> >*;
> >
> >filename xlin DDE *'excel|definitionT!r2c1:r5c2'*;
> >
> >run;
> >
> >*;
> >
> >/* read Excel files using DDE into SAS data set*/
> >
> >*;
> >
> >data test;
> >
> >infile xlin dlm=*'09'x* notab missover dsd;
> >
> >informat id 8. type $20. label $200.;
> >
> >input id type label;
> >
> >format id 8. type $8. label $200.;
> >
> >run;
> >
> >*;
> >
> >/* close Excel workbook and close Excel */
> >
> >data _null_;
> >
> >file xlin;
> >
> >put *'[FILE-CLOSE("N:\PM\PM_RF\CSVs\definitionT.xls")]'*;
> >
> >put *'[QUIT()];
> >
> >run;
> >
> >
> >
> >This code can read "id" and "type" into sas dataset but failed to read
> >"label".(the varible name is in the resulting sas dataset test, but the
> >contents of lable is not there.
> >
> >How can I fix this problem?
> >
> >Thanks.
> >
> >Jeff
> >*
>
| 
