Help using Base SAS procedures

SAS DDE to Excel 2013 freezes SAS

Reply
New Contributor
Posts: 3

SAS DDE to Excel 2013 freezes SAS

Having issues since upgrading to Win 7 and 2013.

Code looks like this.  There also is a close routine, etc.  I added in the open in safe mode to see if that would help.

Any advice would be appreciated.

%macro OpenXL;

options noxwait noxsync;
x ' "C:\Program Files (x86)\Microsoft Office\Office15\EXCEL.exe /s" ';
data _null_;
x=sleep(5);
run;

options noxwait noxsync missing=' ';
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;
%mend;

%macro OpenShell;
filename cmds dde 'excel|system';
data _null_;
    file cmds;
    put '[error(false)]'; /*This suppresses errors that may occur if Excel is already open*/
    put '[open("C:\Template.xlsx")]';
run;
%mend;

%macro write_header(actnum, period, sheetname, range);
   filename data dde "excel|&sheetname!&range" notab;
   data _null_;
   set details_&actnum(obs=1);
   file data lrecl=1000 stopover;
   put actnam;
   put actnum;
   put "&period";
   put '12345';            
   put '12-3456789';       
   run;
%mend;


Super User
Posts: 17,963

Re: SAS DDE to Excel 2013 freezes SAS

Please post the full code submitted.

Also, what version of Excel. I've run DDE on Windows 7 with Excel 2010 and 2013 with no issues.

Super User
Posts: 10,550

Re: SAS DDE to Excel 2013 freezes SAS

One possibly quick thing to look for may be in your environment. If you are operating in a server enviroment then your admin may have disabled the X command. Or it may have been disabled by configuration.

Run code:

 

proc options group=envdisplay;run;

 

And examine results for XCMD. If it does not say something like "The X Command is valid in this SAS session" that would be the first item to address. Note that this option does block DDE if off.

Super User
Posts: 3,115

Re: SAS DDE to Excel 2013 freezes SAS

Does the X command open an Excel session? If not paste the command into a Windows command line to check that it works. I'm assuming here that you are running this on your PC in a local SAS session - this is the only way DDE will work.

New Contributor
Posts: 3

Re: SAS DDE to Excel 2013 freezes SAS

  • I'm having the same issue.
  • The code below works on a computer in my environment with Excel 2010, but freezes SAS on a separate computer with Excel 2013.

 

options noxwait noxsync xmin;

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;

 

New Contributor
Posts: 3

Re: SAS DDE to Excel 2013 freezes SAS

Now I'm getting inconsistent results. The code above froze SAS from a brand new SAS session. I just tried a different program on the Excel 2013 computer with the same components and it worked.
New Contributor
Posts: 3

Re: SAS DDE to Excel 2013 freezes SAS

The issue does not occur when I have Outlook 2013 closed.

 

If this resolves the issue for anyone else, please let us know.

Contributor
Posts: 35

Re: SAS DDE to Excel 2013 freezes SAS

Still freezing is happned with closing Outlook 2013.But DDE worked with closing skype!! (FYI:My environment is Win 7,SAS ver9.4,EXCEL 2013.)

New User
Posts: 1

Re: SAS DDE to Excel 2013 freezes SAS

Skype was the culprit for me too! Thanks very much! Smiley Happy

Ask a Question
Discussion stats
  • 8 replies
  • 1643 views
  • 0 likes
  • 7 in conversation