Inserting SAS Data to excel sheet

[Durjosmok]

Hello!

I'm reaching out for help with the following case. I have the code for putting the SAS data into excel sheet called "data" and "data_transposed" and then save it accordingly with some macro variables. everything seems to be working fine, apart from putting the data into the file "wzor.xlsx" in excel. The program is just opening the file and saving it with other name. 

code:

options noxwait noxsync;
filename sas2xl dde 'excel|system';
data _null_;
length fid rc start stop time 8;
fid=fopen('sas2xl','s');
if (fid le 0) then do;
rc=system('start excel');
start=datetime();
stop=start+10;
do while (fid le 0);
fid=fopen('sas2xl','s');
time=datetime();
if (time ge stop) then fid=1;
end;
end;
rc=fclose(fid);
run;

%macro wstawiaj(nazwa);
data _null_;
length plik $300;
filename ddedata dde
'excel|system';
file ddedata;
plik='[open("'||"&dir_proj"||'wzor.xlsx")]';
put plik;
run;

data _null_;
set do_excela;
filename ddedata dde
'excel|data!r2c1:r37c39' notab;
file ddedata;
put fin_period production forecast_12 months_after_0 months_after_1 months_after_2 months_after_3 months_after_4 months_after_5 months_after_6 months_after_7 months_after_8 months_after_9 months_after_10 months_after_11 months_after_12 months_after_13 months_after_14 months_after_15 months_after_16 months_after_17 months_after_18 months_after_19 months_after_20 months_after_21 months_after_22 months_after_23 months_after_24 months_after_25 months_after_26 months_after_27 months_after_28 months_after_29 months_after_30 months_after_31 months_after_32 months_after_33 months_after_34 months_after_35;
run;

data _null_;
set vintage_transposed;
filename ddedata dde
'excel|data_transposed!r2c2:r34c34';
file ddedata;
put _200601 _200602 _200603 _200604 _200605 _200606 _200607 _200608 _200609 _200610 _200611 _200612 _200701 _200702 _200703 _200704 _200705 _200706 _200707 _200708 _200709 _200710 _200711 _200712 _200801 _200802 _200803 _200804 _200805 _200806 _200807 _200808 _200809 _200810 _200811 _200812;
run;

data _null_;
length plik $300;
filename ddedata dde
'excel|system';
file ddedata;
plik='[save.as("'||"&dir_proj.excel\&p._&z._&g._&v..xlsx"||'")]';
put plik;
put '[close()]';
run;

%mend;
%wstawiaj(testowy);

I have highlighted the code that is not working... Pleas help me 🙂

[Reeza]

I don't see a pause between when you open the file and export, try adding one.

[Durjosmok]

Thank yo so much Reeza for such reply! Can I just ask you to tell me where exactly you can't see a pause? thanks in advance...

[ballardw]

fid=fopen('sas2xl','s');

is the statement that opens the file.

You can add a SLEEP statement as part of the data step to allow time to open. Something like

wait = sleep(5,1); which should wait for 5 seconds.

BTW you may have other applications running that will interfere with DDE. Have you tested or had success with DDE in the past?

[Durjosmok]

Not really... this is a University project. We also tried to use this solution instead..

filename in "&dir_proj.wzor.xlsx";
/* PROXY= is important for going outside firewall, if you have one */
/* proxy="http://yourProxy.company.com" */
;
filename out "&dir_proj.excel\&p._&z._&g._&v..xlsx";
data _null_;
length filein 8 fileid 8;
filein = fopen('in','I',1,'B');
fileid = fopen('out','O',1,'B');
rec = '20'x;
do while(fread(filein)=0);
rc = fget(filein,rec,1);
rc = fput(fileid, rec);
rc =fwrite(fileid);
end;
rc = fclose(filein);
rc = fclose(fileid);
run;

libname xlout XLSX "&dir_proj.excel\&p._&z._&g._&v..xlsx";

data xlout.data;
set do_excela;
put
run;

data xlout.data_transposed;
set vintage_transposed;
run;

libname xlout clear;

It overwrites the data but the fomatting is decimal not percent and we are struggling to change it after the data is loaded to excel

[Reeza · Accepted solution]

Try ODS EXCEL instead, much easier to manage.

I only use DDE for complex reports that have very customized graphs and tables.

[Durjosmok]

that's probably better solution.. thanks anyway!

[Reeza]

This opens the file

data _null_;
length plik $300;
filename ddedata dde
'excel|system';
file ddedata;
plik='[open("'||"&dir_proj"||'wzor.xlsx")]';
put plik;
run;

This writes it immediately after without any pause. Use SLEEP within a data _null_ to add a 2 to 5 second pause.

data _null_;
set do_excela; 
filename ddedata dde 
'excel|data!r2c1:r37c39' notab; 
file ddedata; 
put fin_period production forecast_12 months_after_0 months_after_1 months_after_2 months_after_3 months_after_4 months_after_5 months_after_6 months_after_7 months_after_8 months_after_9 months_after_10 months_after_11 months_after_12 months_after_13 months_after_14 months_after_15 months_after_16 months_after_17 months_after_18 months_after_19 months_after_20 months_after_21 months_after_22 months_after_23 months_after_24 months_after_25 months_after_26 months_after_27 months_after_28 months_after_29 months_after_30 months_after_31 months_after_32 months_after_33 months_after_34 months_after_35;
run;

I'm going to make a big guess that you don't quite understand this code. DDE is a really old technology and there are better ways to export, so if you're making changes consider moving to a different option, if possible.