topic Re: Wrting cells from PROC FREQ into specific cells in an Excel template in SAS Programming

Wrting cells from PROC FREQ into specific cells in an Excel template

MADPhD — Wed, 12 Apr 2023 22:31:02 GMT

I have a Proc Freq that produces a column of 3 numbers (Column name 'N', row 1 name is 'All', row 2 name is 'Female', row 3 name is 'Male'. Column N contains headcounts. The dataset name is B1A.

I want to write these three numbers to an external excel file called 'Final_Table_Test.xlsx; tab name is .Table'.

The Excel file is: filename DDE "excel|C:\...\2020-2021 TABLE\[FINAL_Table_TEST.xlsx]Table!R6C3:R8C3".

At one time (years ago), I I wrote something like this that actually did this.

DATA _NULL_;
set SANDBOX.B1A;
INFILE 'B1A';
input N;
FILE FINAL_Table_TEST;
PUT CELL1 CELL2 CELL3;
RUN;

However, no manipulation I have tried works. I get the following error:

ERROR: Physical file does not exist, C:\WINDOWS\system32\B1A.
ERROR: Insufficient authorization to access C:\WINDOWS\system32\FINAL_TABLE_TEST.

Any ides?

Re: Wrting cells from PROC FREQ into specific cells in an Excel template

Reeza — Wed, 12 Apr 2023 22:40:31 GMT

DDE was deprecated by Windows a while ago due to security concerns. You can re-enable it in Excel but you should check with your IT Security team first.

My recommendation would be to export the data to a new sheet that SAS can overwrite to each time, and then link those cells to the location you want instead. I haven't tried this in a while but theoretically it worked a few years ago 🙂

Re: Wrting cells from PROC FREQ into specific cells in an Excel template

Ksharp — Thu, 13 Apr 2023 11:44:25 GMT

proc summary data=sashelp.heart ;
class sex;
output out=have ;
run;
data _null_;
set have;
if missing(sex) then call symputx('all',_freq_);
else call symputx(sex,_freq_);
run;




libname x excel 'c:\temp\FINAL_Table_TEST.xlsx' scan_text=no getnames=no; 
proc sql;
update x.'sheet1$'n
set f2="&all"
where lowcase(f1)='all students'
;
update x.'sheet1$'n
set f2="&female"
where lowcase(f1)='females'
;
update x.'sheet1$'n
set f2="&male"
where lowcase(f1)='males'
;
quit;
libname x clear;

Re: Wrting cells from PROC FREQ into specific cells in an Excel template

MADPhD — Thu, 13 Apr 2023 19:51:30 GMT

Ksharp, I owe you a steak dinner, or at least a drink! Your solution almost worked. I made a few simple trial&error modifications and it works like a charm. Thanks for the lead-in!

proc summary data=Table1 ;
class sex;
output out=SANDBOX.B1A;
run;

[cid:4eab63a2-af30-43d2-9250-91fcf5d23b1f]

data _null_;
set SANDBOX.B1A;
if missing(sex) then call symputx('all',_freq_);
else call symputx(sex,_freq_);
run;

libname x excel 'C:\Users\...\FINAL_Table_TEST.xlsx' scan_text=no getnames=no;

proc sql;
update x.'Table$'n set f2="&all" where (f1)='all students';
update x.'Table$'n set f2="&F" where (f1)='Females' ;
update x.'Table$'n set f2="&M" where (f1)='Males';
quit;
libname x clear;

[cid:d441740a-9564-4311-b90e-3cc2ce0eda66]

Re: Wrting cells from PROC FREQ into specific cells in an Excel template

Ksharp — Fri, 14 Apr 2023 11:52:29 GMT

Hope one day I could have that "steak dinner" . *^_^*