SAS 9.4 and issues between operating systems

Reply
Occasional Contributor
Posts: 14

SAS 9.4 and issues between operating systems

I have just installed 9.4TS1M2 onto my desktop running windows 7 pro, and has 9.3TS1M0 installed. I had no problem with installation and everything seems to be working fine.

I installed this version of 9.4 onto my laptop running windows 8.1 pro. i had no problem with installation. I am having problems running a program that contains macros with dde excel statements. SAS completely freezes and i have to force quit. This same program run on the desktop in 9.4, has no problems.

Both computers are using office 365 / excel 2013.

Any ideas?

Super User
Posts: 19,772

Re: SAS 9.4 and issues between operating systems

The DDE program will probably need different paths to the Excel Executable for one thing, has this been modified in the program on each of the systems?

Occasional Contributor
Posts: 14

Re: SAS 9.4 and issues between operating systems

There are no paths to the Excel Executable. In the auto exec program, the directories are named.

i have tried changing the file i am trying to write to to xlsx but this did not fix the problem.

this the program. some information is redacted.

%let datafile_newest   = "C:\Projects\data.xlsx";

proc import out = data_20150422_import

    datafile = &datafile_newest

    dbms = excel;

    sheet = "Sheet1";

    getnames = yes;

run;

data rawdata.data_20150422;

    set data_20150422_import;

    drop f9 stratvar1 startvat2;

    if siteid = . then delete;  

    rename censored = censored_excel;

    if subjid eq 'xxxxx' then delete;  /*this patient didd not sign informed consent */

   

run;

data filler;

input months @@;

fcount=0; ccount=0;

cards;

0  1 2  3  4  5  6  7  8  9

10 11 12 13 14 15 16 17 18 19

20 21 22 23 24 25 26 27 28 29

30 31 32 33 34 35 36 37 38 39

40 41 42 43 44 45 46 47 48 49

50 51 52 53 54 55 56 57 58 59

60 61 62 63 64 65 66 67 68 69

70 71 72 73 74 75 76 77 78 79

80 81 82 83 84 85 86 87 88 89

90 91 92 93 94 95 96 97 98 99

100 101 102 103 104 105 106 107 108

;

run;

filename out "&dir&client&project\output\xxxx.rtf"; *dm 20150420 changed file name;

*proc printto file=out new;

ods rtf file=out;

ods graphics on;

options ls=120 ps=45 pageno=1;

*%let dtdb=20150105;

%let dtdb=20150422;

%let select=  ;

/*

proc freq data= rawdata.data_20150417;

    table censored_excel;

run;

*/

data temp1_;

        set rawdata.data_&dtdb;

        siteid=1*siteid;

         &select;

        if subjid eq 'xxxxx' then do;

            censored = 1;

            da7stofail = (dt_last - dt_surgery);

            months =  floor(da7stofail/30.25);

            monthsf = da7stofail/30.25;

        end;

        if subjid eq 'yyyyy' then do;

            censored = 1;

            da7stofail = (dt_last - dt_surgery);

            months =  floor(da7stofail/30.25);

            monthsf = da7stofail/30.25;

        end;

       

        if dt_last eq . then  do;

            censored = 1;

            if siteid = 1 then do;

                da7stofail = ('31MAR2015'd - dt_surgery);

               end;

            if siteid = 2 then do;

                da7stofail = ('31MAR2015'd - dt_surgery);

               end;

            if siteid = 3 then do;

                da7stofail = ('10DEC2014'd - dt_surgery);

               end;

            if siteid = 4 then do;

                da7stofail = ('31MAR2015'd - dt_surgery);

               end;

            if siteid = 6 then do;

                da7stofail = ('31MAR2015'd - dt_surgery);

               end;

               

            months =  floor(da7stofail/30.25);

            monthsf = da7stofail/30.25;

        end;

        if censored = . then do;

            censored = 0;

            da7stofail = (dt_last - dt_surgery);

            months =  floor(da7stofail/30.25);

            monthsf = da7stofail/30.25;

        end;

         drop da7stofail /* V04_16_2013*/;

         drop __31_2015 _2_10_2014 ;

run;

           

title3 'a';

title4 'Distributions of SiteID and Censored';

proc freq data=temp1_;

tables siteid censored;

tables siteid*censored/nopercent nocol;

run;

title4 'Followup time (months) in Failed and Censored Devices - overall';

proc means data=temp1_ n mean median q1 q3 min max maxdec=1;

var monthsf;

class censored;

run;

title4 'Followup time (months) in Failed and Censored Devices - by site';

proc means data=temp1_ n mean median q1 q3 min max maxdec=1;

var monthsf;

class siteid censored;

run;

%macro doit(sheet, select);

title4 "&select";

data temp1;

        set temp1_;

        &select;

        run;

title5 'Followup time (mnoths) in Failed and Censored Devices';

proc means data=temp1 n mean median q1 q3 min max maxdec=1;

var monthsf;

class censored;

run;

title5 'Listing of Subjects with Failures';

proc print data=temp1 ;

where censored=0;

var siteid subjid dt_surgery dt_last monthsf;

run;

title4 'Survival Analysis';

proc lifetest plots=(survival) data=temp1 /*lineprinter*/  METHOD=PL;

        time monthsf*censored(1);

        id subjid;

               

   /*strata group*/ /*&var*/    ;

              run;

       * time mtime*censor(1);

       * id siteid subjid;

run;

ods output      ProductLimitEstimates   =PLE

                        CensoredSummary                 =CS;

title4 'Survival Analysis for export to life tables';

proc lifetest data=temp1 /*lineprinter*/  outsurv=moab METHOD=PL ;

        time months*censored(1);

        id subjid;

                   /*strata group*/ /*&var*/    ;

                run;

       * time mtime*censor(1);

       * id siteid subjid;

run;

ods output;

data failed_count;

set PLE(where=(survival is not missing));

fcount=dif(failed);

run;

proc freq data=PLE;

where censor=1;

tables months/out=censored_count (rename=(count=ccount));

run;

filename cs dde "Excel|[Tables_Survival.xlsx]&sheet!R3C2" notab;

data _null_;

set CS;

file cs;

put total;

run;

*filename survtab dde "Excel|[Tables_Survival.xls]&sheet!R3C10:R85C12" notab;

filename survtab dde "Excel|[Tables_Survival.xls]&sheet!R3C10:R121C12" notab;

data survtable1;

merge censored_count failed_count;

by months;

run;

data survtable2;

update filler survtable1;

by months;

file survtab;

put months '09'X      fcount '09'X ccount '09'X

        ;

run;

%mend doit;

%doit(select=, sheet=All);

%doit(select=if siteid=1, sheet=Site1);

%doit(select=if siteid=2, sheet=Site2);

%doit(select=if siteid=3, sheet=Site3);

%doit(select=if siteid=4, sheet=Site4);

%doit(select=if siteid=6, sheet=Site6);

ods rtf close;

Super User
Posts: 19,772

Re: SAS 9.4 and issues between operating systems

Try explicitly opening Excel then in the 8.1 and see if it helps.  I don't have 8.1 to test but I also haven't seen DDE without the Excel system call...something for me to test tomorrow on Windows 7 Smiley Happy

Occasional Contributor
Posts: 14

Re: SAS 9.4 and issues between operating systems

When using DDE i always have to have the specific excel file open on the computer. This procedure i have not changed on either OS.

Does having 9.3 on the computer where the program works having thing to do with it?

Super User
Posts: 19,772

Re: SAS 9.4 and issues between operating systems

dgm1012 wrote:

When using DDE i always have to have the specific excel file open on the computer. This procedure i have not changed on either OS.

Ah, that's how you get around calling the executable.

Generally I use commands to open Excel, have it open the file and then there's no worry about that. I've always found I do have to customize it though for each system.  This may come close to what you need.

options noxwait noxsync;

x '"C:\Program Files\Microsoft Office 15\root\office15\EXCEL.EXE"';

data _null_;

  z=sleep(5);

run;

filename cmds dde 'excel|system';

data _null_;

file cmds;

put '[open("Tables_Survival.xls")]';

x=sleep(2);

run;


filename random dde 'excel|sheet1R3C10:R121C12';

data survtable2;

update filler survtable1;

by months;

file random;

put months '09'X      fcount '09'X ccount '09'X

        ;

run;

data _null_;

file cmds;

put '[close(0)]';

put '[quit()]';

run;


filename random;

filename cmds;

Occasional Contributor
Posts: 14

Re: SAS 9.4 and issues between operating systems

Thanks Reeza.

I tried this, but no luck.

To clarify:

I have used this method before to open up excel, and so your method does work, and I do not have to manually open the file. But, when I add what you suggested to my code, Excel opens, but SAS still freezes up.

Super User
Posts: 19,772

Re: SAS 9.4 and issues between operating systems

I guess I'd try the examples in the documentation. If it freezes up at that then I would contact tech support.

Super User
Posts: 7,766

Re: SAS 9.4 and issues between operating systems

As DDE is more or less deprecated and replaced by other options, and Microsoft has a long and bitter history of mucking around with their API's across Windows releases, I would contemplate switching to more stable methods of communicating data to Excel.

My experience has taught me that the only reliable way to get data into Excel is the use of a textual external file format like csv. Everything else fails sooner or later.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Occasional Contributor
Posts: 14

Re: SAS 9.4 and issues between operating systems

Posted in reply to KurtBremser

I agree, and I have switched over from DDE for all the projects that I am in charge of from the beginning. Unfortunately, a few of our programmers still manage some very big projects built using DDE. As you can see in the program, lineprinter, was previously. These are "old school."

Due to time constraints, and budget constraints, and not having a problem using DDE, except on 9.4 under windows 8.1, there is no need/ability to force these projects to upgrade.

Frequent Learner
Posts: 1

Re: SAS 9.4 and issues between operating systems

Did you ever find a solution to this?

Ask a Question
Discussion stats
  • 10 replies
  • 1011 views
  • 0 likes
  • 4 in conversation