topic Re: Write SAS data to a specific excel cell in SAS Programming

Write SAS data to a specific excel cell

scb — Mon, 13 May 2019 09:51:32 GMT

data _null_;
call symput('period',put(intnx('month',today(),-3),yymmn6.));
run;

May I know how to write the 'period' (201902) into a specific cell in Excel? Let say to cell E3.

Thanks.

Re: Write SAS data to a specific excel cell

Reeza — Mon, 13 May 2019 15:50:34 GMT

You can't. You can export a data set but that will add the column header. I don't believe there's a way to have a single value put to a cell besides other alternatives that are well documented on here. DDE was the old method which still technically works but I would never recommend building anything relying on that technology.

Re: Write SAS data to a specific excel cell

DWilson — Fri, 17 May 2019 14:45:45 GMT

Use SAS/IML to call R

Pass your data to R via SAS/IML

Pass R code to R via SAS/IML to do the writing to Excel for you

Use the openxlsx or XLConnect R packages to do the excel writing in R

(Not a great answer I know. the only SAS-program driven solution I've been able to find. The other solutions involve writing VBA code in Excel to pull data into excel versus pushing data to excel from a SAS program.)

See this paper for example code to get you started:

https://analytics.ncsu.edu/sesug/2017/SESUG2017_Paper-109_Final_PDF.pdf

Re: Write SAS data to a specific excel cell

noling — Fri, 17 May 2019 13:03:48 GMT

This isn't elegant, but you could brute force it with something like:

%let value=201902;
data temp;
	length a b c d e 8.;
	output; *row 1;
	output; *row 2;
	e=&value;
	output; *row 3;
run;
proc export data=temp
	outfile="path\temp.xlsx"
	dbms=xlsx
	replace;
	putnames=no; *no header row;
run;