How to output file name to every sheet (excel)

[dgm5028]

I would like to attach to last line on every excel sheet the name of the SAS program that is populating the cells. This row is not constant for each sheet. and I am using multiple %tables (see below) on each sheet.

Here is an example of one table

%tables

(data=vasbk_censored, varlist=vasbk,group=grp,xlfile=tables_clinical_followup_vas, sh=BK Pain,grp_a=1,grp_b=2, rs=5,re=8);

I know that   put %sysget(SAS_EXECFILENAME);   will give me the name of program.

But I am wondering whether there is a way to have sas search for the last line of each excel sheet (last line of the bottom table), and then on the next line place something like the following

[Source: Tables_Safety_ae_by_time_DevRelx.sas]

[LinusH]

What method are you using for the population of the Excel sheets?IIf you are using ODS you could try (not tested ) FOOTNOTE.

[RW9]

Yes, what is %tables and what does it do.  I would suggest using proc report with ods tagsets.excelxp.  You can then do (if you want it in the data) - note that using title/footnote will put it in the titles/footnotes of Excel, these can be seen when printed but not normally in the grid itself.

ods tagsets.excelxp file="abc.xlsx" style=statistical;

proc report data=xyz...;

     column...;

     define ...;

     compute after;

          line %sysget(sas_execfilepath);

     endcomp;

run;

ods tagsets.excelxp close;

[Reeza]

Use tagsets.excelXP option embedded_footnotes to have the footnote in the sheet.

[dgm5028]

I am not using ODS. I am using DDE.

I need it in the actual sheet, although I now realize that it does not matter if it is at the end of the tables. I could set the default placement row at 100 for example, and then hide rows in between.

The reason I must do this in this method, is that when all the tables are created, they are then copied and pasted in various reports, not printed from excel. But I believe that setting a default row, say 100, and then hiding rows would work as well. An automatic find last row method would be extremely time saving, but may not be possible?

Here is one section of code that creates a particular column in one of the sheets.

filename case           dde "Excel|[&xlfile..xls]&sh!R&rs.C7: R&re.C7"   notab;

filename control        dde "Excel|[&xlfile..xls]&sh!R&rs.C13:R&re.C13" notab;

data _null_ ;

set max2;

file case;      put h&grp_a;

file control;   put h&grp_b;

run;

[dgm5028]

I am using DDE to populate tables. for example, the following code populates the column "mean".

filename case       dde "Excel|[&xlfile..xls]&sh!R&rs.C3:R&re.C3"  notab;

filename control        dde "Excel|[&xlfile..xls]&sh!R&rs.C9:R&re.C9"  notab;

data _null_ ;

set means2;

file case;      put a&grp_a;

file control;   put a&grp_b;

run;

I have not used tagsets in the past. Where would this be entered into the code? More importantly, where are the footnotes placed in the sheet?

I need to have this "source" line in a cell showing at all times, not just when printing, as when the tables are created, I copy and paste them into other programs in finalizing project for client, i do not print from excel.

[RW9]

So if your using DDE why not just ascertain how many observations you have then output one more with your footnote?

Anyways, DDE is quite old now, may not be available in the future.  Would really suggest you investigate other ways of doing what you need to with Excel - and to be fair Excel is not a tool for <insert your task here> in the first place, so you will hit a fair few obstacles.

[dgm5028]

What other tool would you recommend for this purpose?

From what Ive gathered there is not another method for table population that as easily lets you specify cells.

I've played a bit with named ranges but this seems extremely time consuming to have to manually create ranges in excel before being able to place information into them.

[Reeza]

If it doesn't matter where your source is because it's linked, why not store the information in a separate table and export that out at the end in its own sheet? Sort of a index of tables?

I don't think this will answer your question but here's another way. I think this has been updated on sascommunity.org

http://support.sas.com/resources/papers/proceedings14/1793-2014.pdf

[RW9]

Well, if your stuck in a situation where you have some Excel file in which you need to place certain data in different places, then my recommendation would be to do the necessary programming in Excel.  Use SAS to create an output file, CSV or XLSX.  Then create VBA macro to import and process the data.  Its very simple VBA (Visual Basic for Applications) and there are hundreds of examples out there for any possible scenario you want.  You then have full access to the full Office Object (ie. the entire Office suite):

http://www.zerrtech.com/content/excel-vba-open-csv-file-and-import

https://msdn.microsoft.com/en-us/library/office/aa221353(v=office.11).aspx

http://www.excel-easy.com/vba/loop.html

The reason is that SAS produces outputs, and data transfer files.  You are trying to use an output file as more than that, perhaps as dashboard, or other application.  It something I see a lot of, I need to do word processing, open Excel, need to do databasing, open Excel, need to paint my new masterpiece, you guessed it - open Excel.  Its probably the most abused software available today.