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!
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 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.
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).
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;
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...
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.