How to make PC SAS DDE Sever Code to communicate with Windows 10 Excel 2016

0
votes

I had set up a code in PC SAS in my Windows 7 having Excel 2010 and PC SAS 9.4. Basically what the code did was take the dataset 'wide2' and paste it to the only excel file which is open in the OS at that moment and then save it. Following is the code which I was using:

%macro paste(number);


proc print data=wide2;
run;

PROC CONTENTS DATA=wide2 /*NOPRINT*/ OUT=CNT ;
RUN;
PROC SORT DATA=CNT ;
BY VARNUM ;
RUN;


PROC SQL /*NOPRINT*/;
SELECT NAME
INTO: VARS SEPARATED BY ' '
FROM CNT ;
SELECT COUNT(DISTINCT NAME)
INTO: COLS SEPARATED BY ' '
FROM CNT ;
SELECT NOBS
INTO: ROWS
FROM CNT
WHERE VARNUM = 1;
QUIT;



proc print data=cnt;run;


FILENAME TEMP DDE "EXCEL|Sheet&number.!R1C1:R200C200" ;


data _null_ ;
  file temp ;
  if _n_=1 then do;
    do _n_=1 to &cols ;
      set cnt(keep=name rename=(name=__name__)) ;
      put __name__ @;
    end;
    put;
  end;
  set wide2 ;
  put &vars ;
run ;
%mend paste;

So when I executed a macro like %paste(1), it would paste the data to Excel's Sheet1.

Now we have moved to Windows 10 which has the same PC SAS version but Excel 2016. When I run the same code with the same dataset, the PC SAS gets hung for an eternity after a couple of seconds and then I have to just kill the process. Nothing happens to the open excel file. I can't even see the log since the PC SAS gets hung. Please suggest a solution for this.( The top bar of the PCSAS when it gets hung says 'running DATA Step')

1
excelautomationsaswindows-10dde
There was a security update in Dec 2017 that disabled DDE for Excel. You need to go into Excel, security settings and reenable DDE. Then try again. You also need to ensure you have xcmd option enabled. I usually at least have SAS open Excel to ensure commands are being passed correctly as well. If you can add that line for testing it helps to debug your code. If you need specific instructions on enabling DDE see some of the other DDE SAS posts this year. – Reeza

1 Answers

0
votes

How many columns and rows in the wide2 data set ? Your posted code ran on my system with SAS 9.4M4 and Office 365. I used this data set.

data wide2;
  do id=1 to 3;
    array x(15) (1:15);
    output;
  end;
  format _numeric_ 2.;
run;