DATA Step, Macro, Functions and more

Updating excel cells from sas-proc sql

Accepted Solution Solved
Reply
Contributor
Posts: 33
Accepted Solution

Updating excel cells from sas-proc sql

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


Accepted Solutions
Solution
‎09-08-2012 02:54 PM
Frequent Contributor
Posts: 86

Re: Updating excel cells from sas-proc sql

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.

View solution in original post


All Replies
Solution
‎09-08-2012 02:54 PM
Frequent Contributor
Posts: 86

Re: Updating excel cells from sas-proc sql

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.

Contributor
Posts: 33

Re: Updating excel cells from sas-proc sql

Thank-You...

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

Super User
Posts: 9,687

Re: Updating excel cells from sas-proc sql

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

Contributor
Posts: 33

Re: Updating excel cells from sas-proc sql

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.

Super User
Posts: 9,687

Re: Updating excel cells from sas-proc sql

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

Contributor
Posts: 33

Re: Updating excel cells from sas-proc sql

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.

Super User
Posts: 9,687

Re: Updating excel cells from sas-proc sql

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

Contributor
Posts: 33

Re: Updating excel cells from sas-proc sql

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.

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 8 replies
  • 1532 views
  • 3 likes
  • 3 in conversation