topic Updating excel cells from sas-proc sql in SAS Programming

Updating excel cells from sas-proc sql

RaviSPR — Sat, 08 Sep 2012 18:03:46 GMT

hi..i want to update specific excel cells from sas using Proc sql..is it possible??

if yes, i need to update range of excel values using proc sql...i dont want to use SAS-DDE..means dont want to open excel file from sas..rather i just can connect to excel through proc sql...

finally am struggling to how to give range of excel cell in proc sql to so that i can put data in excel from sas..?ls

Re: Updating excel cells from sas-proc sql

bnarang — Sat, 08 Sep 2012 18:54:43 GMT

You can create library and map this excel file in the library. Further, you can modify once the datasheets are visible as datasets using PROC SQL.

Re: Updating excel cells from sas-proc sql

Ksharp — Mon, 10 Sep 2012 05:11:32 GMT

As above said.

Once you assign a libname to the excel file successfully, You can modify cell like a sas dataset.

libname x excel 'D:\x.xls' scantext=no;

proc sql;

update x.'Sheet1$'n

set _col0='xxx'

where _col2='my condition';

quit;

Ksharp

Re: Updating excel cells from sas-proc sql

RaviSPR — Mon, 10 Sep 2012 17:25:39 GMT

Thank-You...

i think we need to use Named range also in the excel files.

Re: Updating excel cells from sas-proc sql

RaviSPR — Mon, 10 Sep 2012 17:33:52 GMT

Thanks a lot...

yes. its correct.

i got a way to write data to specific excel cells.But am getting variable names into the xl file those i dont want.

below is the code.

Here namedrng1 is the Named range in the excel file contains specific cells in a sheet.

Libname xl excel "Path";

Data xl.namedrng1;

set work.emp;

run;

here am getting columns names into xl file..is there any way to remove sas dataset's(work.emp) column names?

I can hide these columns in the excel file...BUT....still....????

Regards,

SPR.

Re: Updating excel cells from sas-proc sql

Ksharp — Tue, 11 Sep 2012 02:43:17 GMT

If your excel file is in english, try to use XLS engine.

proc export data=sashelp.class outfile='c:\want.xls' replace dbms=xls;

putnames=no;run;

Ksharp

Re: Updating excel cells from sas-proc sql

RaviSPR — Tue, 11 Sep 2012 06:29:25 GMT

Sorry Ksharp. if we use proc export we cant write data to required cell ranges in the excel...!! right??

is any further add-ons for my code which i mentioned above.i.e Libname and Proc SQL connect to excel, to eliminate column names while writing data into the cell ranges??

Thanks a lot.

Re: Updating excel cells from sas-proc sql

Ksharp — Tue, 11 Sep 2012 07:10:03 GMT

My SQL 's update statement will not get extra variable name . Did you try it ?

Re: Updating excel cells from sas-proc sql

RaviSPR — Tue, 11 Sep 2012 17:45:14 GMT

But your code writes the data by checking condition with the excel cell values.

Actually, i want to write/export data which is coming from sas datasteps. so, i cant check the conditions with the values presented in the excel.

i.e its entirely new data for the new month which should be written to excel excel cell ranges.

The problem here is unwanted column ranges..But for time being i am hiding those columns in the template file.

Thanks,

SPR.