Updating excel cells from sas-proc sql

[RaviSPR]

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

[bnarang · Accepted solution]

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.

[RaviSPR]

Thank-You...

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

[Ksharp]

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

[RaviSPR]

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.

[Ksharp]

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

[RaviSPR]

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.

[Ksharp]

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

[RaviSPR]

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.