02-08-2015 08:17 PM
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
(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
02-09-2015 05:04 AM
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...;
ods tagsets.excelxp close;
02-09-2015 08:12 AM
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_ ;
file case; put h&grp_a;
file control; put h&grp_b;
02-09-2015 09:10 AM
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_ ;
file case; put a&grp_a;
file control; put a&grp_b;
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.
02-09-2015 01:10 PM
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.
02-09-2015 06:44 PM
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.
02-09-2015 11:54 PM
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
02-10-2015 04:03 AM
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):
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.