BookmarkSubscribeRSS Feed
kwozni3
Calcite | Level 5

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:

kwozni3_1-1616112145215.png

 

 

Into these 12 cells in my excel sheet:

kwozni3_0-1616111724724.png

 

 

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. 

 

kwozni3_2-1616112424693.png

 

 

Any ideas would be really great. Thank you!

8 REPLIES 8
ChrisNZ
Tourmaline | Level 20

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
Calcite | Level 5
Hi Chris,

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

Thanks,
Kelli
ballardw
Super User

@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:

kwozni3_1-1616112145215.png

 

 

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;

 

kwozni3_2-1616112424693.png

 

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
Calcite | Level 5
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
Super User Tom
Super User

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
Calcite | Level 5
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
PROC Star

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
Obsidian | Level 7

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...

Ming Zou, MD PhD
SAS Hackathon 2022 - RepTik Analytics Solution, www.reptik.swiss
Easy report generation cross-platform. Create, format, and modify your tables & reports with the ease of Office Software and fill in with data from any source automatically via RepTik PDR technology.

SAS Innovate 2025: Call for Content

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!

Submit your idea!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 8 replies
  • 3794 views
  • 0 likes
  • 6 in conversation