DDE (Excel) determine last row

Accepted Solution Solved
Reply
Contributor
Posts: 21
Accepted Solution

DDE (Excel) determine last row

Hallo,

I try to create a report where I append my result to an existing sheet. e.g. a new line for each day the report is created.  Now I am trying to find the last filled row to define the range to add the new result.

I know there is an excel formula like: =AGGREGATE(14,4,NOT(EMPTY(B:B))*ROW(B:B),1)

But I don't know how to pass a row number to a sas macro variable to use it in a filename statement.

I hope someone could help me.

Christoph


Accepted Solutions
Solution
‎04-22-2015 04:53 AM
Super User
Super User
Posts: 7,401

Re: DDE (Excel) determine last row

To my mind you are just creating more work for yourself.  Read the existing data into SAS.  Append any new data.  Then write a new output file.  To note, DDE is old and may not be supported, and certainly doesn't have the full feature list of Excel after Excel98, so I wouldn't recommend using it.  TBH I wouldn't recommend using Excel full stop. For data transfer use transfer format files XML, CSV, Dataset, XPT etc,  If its a report use the tagsets.excelxp to write out files.  You can also use libname to Excel (which in 9.4 has XLSX), and use that to read/write like a dataset. 

View solution in original post


All Replies
Solution
‎04-22-2015 04:53 AM
Super User
Super User
Posts: 7,401

Re: DDE (Excel) determine last row

To my mind you are just creating more work for yourself.  Read the existing data into SAS.  Append any new data.  Then write a new output file.  To note, DDE is old and may not be supported, and certainly doesn't have the full feature list of Excel after Excel98, so I wouldn't recommend using it.  TBH I wouldn't recommend using Excel full stop. For data transfer use transfer format files XML, CSV, Dataset, XPT etc,  If its a report use the tagsets.excelxp to write out files.  You can also use libname to Excel (which in 9.4 has XLSX), and use that to read/write like a dataset. 

Contributor
Posts: 21

Re: DDE (Excel) determine last row


I was so focused to find a way to determine the last row that I did not see how easy it is reading the existing data. Smiley Happy

Thanks.

Christoph

☑ This topic is SOLVED.

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

Discussion stats
  • 2 replies
  • 265 views
  • 0 likes
  • 2 in conversation