Export SAS Data to Specific Cells in a Preexisting Excel Sheet

[kwozni3]

Hi everyone! 

First-time poster, so I'm hoping this works. 

I have an assessment form that I would like to auto-populate using SAS. I have created this form (read: made column titles and rows) in excel, and have written the SAS code to get the numbers that I want to add to said form. Now I would like SAS to send said data to the corresponding cells in my excel sheet. Basically, I'd to be able to update this form weekly without having to manually copy and paste my SAS proc freq results into excel. 

The closest I have come to a solution through googling is to use SAS DDE, with the steps I found here: https://stats.idre.ucla.edu/sas/faq/how-can-i-send-sas-dataresults-to-specific-cells-in-an-excel-spr... in example #2. 

Basically I want to be able to tell SAS to put these 12 numbers from proc freq:

Into these 12 cells in my excel sheet:

This is what I have tried so far:

proc freq data=merged3 noprint;
tables CT_name/nopercent nocum out=tc_count3;
run;
options noxwait noxsync;
%sysexec '"C:\CBO Reports\ASM.xlsx"';
data _null_;
rc=sleep(5);* wait for 5 seconds;
run;
filename ada dde 'excel|[ASM.xlsx]Sheet1!r8c2:r19c2';
data tc_count3;

set tc_count3;
file ada;
if _stat_ = "COUNT" then put score;
run;

I've also tried it with this block, using x instead of %sysexec:

options noxwait noxsync;
x '"C:\CBO Reports\ASM.xlsx"';
filename example2 dde 'excel|[ASM.xlsx]Sheet1!r8c2:r19c2';
data tc_count3;
set tc_count3;
file example2;
if _stat_ = "MEAN" then put score;
run;

When I do the first, it successfully opens my excel sheet (so that part works) but it's just not adding the numbers anywhere. There doesn't seem to be an error message either. It just says there were 0 observations read into the excel sheet. 

Any ideas would be really great. Thank you!

[ChrisNZ]

You need to export to an Excel range.

Also DDE is no longer supported by Microsoft.

Many replies on this topic here, try the solutions proposed and come back to us with any issue.

[kwozni3]

Hi Chris,

If DDE does not work, do you know of a different code that might?

Thanks,
Kelli

[ballardw]

---

@kwozni3 wrote:

Hi everyone! 

First-time poster, so I'm hoping this works. 

I have an assessment form that I would like to auto-populate using SAS. I have created this form (read: made column titles and rows) in excel, and have written the SAS code to get the numbers that I want to add to said form. Now I would like SAS to send said data to the corresponding cells in my excel sheet. Basically, I'd to be able to update this form weekly without having to manually copy and paste my SAS proc freq results into excel. 

The closest I have come to a solution through googling is to use SAS DDE, with the steps I found here: https://stats.idre.ucla.edu/sas/faq/how-can-i-send-sas-dataresults-to-specific-cells-in-an-excel-spr... in example #2. 

Basically I want to be able to tell SAS to put these 12 numbers from proc freq:

Into these 12 cells in my excel sheet:

This is what I have tried so far:

proc freq data=merged3 noprint;
tables CT_name/nopercent nocum out=tc_count3;
run;
options noxwait noxsync;
%sysexec '"C:\CBO Reports\ASM.xlsx"';
data _null_;
rc=sleep(5);* wait for 5 seconds;
run;
filename ada dde 'excel|[ASM.xlsx]Sheet1!r8c2:r19c2';
data tc_count3;

set tc_count3;
file ada;
if _stat_ = "COUNT" then put score;
run;

I've also tried it with this block, using x instead of %sysexec:

options noxwait noxsync;
x '"C:\CBO Reports\ASM.xlsx"';
filename example2 dde 'excel|[ASM.xlsx]Sheet1!r8c2:r19c2';
data tc_count3;
set tc_count3;
file example2;
if _stat_ = "MEAN" then put score;
run;

Any ideas would be really great. Thank you!

---

The log you show a picture of is pretty clear. The variable used in IF to determine when to output values has no values and likely was created in that If (that is what VARIABLE _STAT_ IS UNINITIALIZED means) so the PUT never executes. Therefore nothing is written. Further, the SCORE variable is uninitialized as well and would have had no value to put to the file so you would have gotten nothing but the current "missing" character, likely a dot, in the output.

Please post code and log entries as copied text pasted into a text box opened on the forum with the </> icon. That preserves formatting of text and allows us to copy and paste corrections or highlights. Pictures not so much. Should be simpler as well.

[kwozni3]

Hi there,

So, part of the problem (and I realize I was kind of dumb for doing this) is that I don't understand all of the coding that I tried. I *thought* what the above IF statement was saying was: "if the statistic in the table is COUNT then put its value (score) into excel." I'm assuming that is not actually the case.

So, if I want to take the counts given to me in a proc freq table and send them into specified rows in excel, is there a way to format that?

Thanks,
Kelli

Could you help me to format that part of the code to make it work?

[Tom]

You are confusing the output of PROC MEANS and the output of PROC FREQ.

When you are not sure what is in a dataset just LOOK at the dataset and check.  (PROC CONTENTS, PROC PRINT, etc).

[kwozni3]

Hi Tom,

I only need to export my proc freq count data. (If I alluded to proc means elsewhere that was done by mistake). Do you know of any coding that might do that? If so, would you mind providing me with an example? Or if you need more information from me please let me know!

[SASKiwi]

Here is an example of getting close to what you want. You can then use an Excel macro to copy the data in the load sheet Cars to the specific cells you want:

proc freq data = sashelp.cars;
  table make / nocum nopercent out = cars (keep = count);
run;

proc export data  = cars  
            file = "C:\MyDir\Cars.xlsx"
            dbms = EXCEL 
            replace
            ;
  sheet = "Cars";
RUN;

[Bravez]

This new solution may help - you can put values to any cells of choice in Excel/ Word/ PowerPoint: One-Click Report Automation - An automated and user friendly workflow for efficient, flexible, and e...