topic Re: How to determine the last cell in Excel using SAS? in SAS Programming

How to determine the last cell in Excel using SAS?

angeliquec — Thu, 04 Dec 2014 07:19:12 GMT

Hi!

For example, I have this 4 X 4 table in SAS (it has 4 columns, and 4 rows excluding the headers).

If exported to excel, the range would be translated to A1:D5.

I need help how to get the D5 value.

Thank you!

Re: How to determine the last cell in Excel using SAS?

Reeza — Thu, 04 Dec 2014 07:54:47 GMT

You could manually calculate it.

Why though? Excel also supports RC notation which may be easier to derive. RC=row/column so A1:D5 would be R1C1:R5C4

Re: How to determine the last cell in Excel using SAS?

user24feb — Thu, 04 Dec 2014 09:15:02 GMT

If this is about the text "D5", then you could calculate it in SAS using the dataset you would like to export:

Data A;
Input X1-X4;
Datalines;
1 1 1 1
1 1 1 1
1 1 1 1
1 1 1 1
;
Run;

Data xx;
Length Text_Col $2.;
MyFile=Open("A");
Rows=Attrn(MyFile,"NObs")+1; * including Header;
Cols=Attrn(MyFile,"NVars");
If (Cols le 26) Then Text_Col=Byte(64+Cols);
Else If (Cols ge 27) & (Cols le 676) Then Text_Col=(Byte(64+Int(Cols/26)))!!Byte(64+Mod(Cols,26));
ExcelCell=Compress(Text_Col!!Put(Rows,Best8.));
Put "**" ExcelCell "**"; * "D5" is in the log ... ;
MyFile=Close("A");
Run;

Re: How to determine the last cell in Excel using SAS?

RW9 — Thu, 04 Dec 2014 09:33:03 GMT

Hi,

What is the purpose, are you trying to write to a specific range? I fso then you may be better of with libname to Excel.