BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
angeliquec
Quartz | Level 8

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!

1 ACCEPTED SOLUTION

Accepted Solutions
user24feb
Barite | Level 11

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;

View solution in original post

3 REPLIES 3
Reeza
Super User

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

user24feb
Barite | Level 11

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;

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

hackathon24-white-horiz.png

2025 SAS Hackathon: There is still time!

Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!

Register Now

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 3 replies
  • 1520 views
  • 0 likes
  • 4 in conversation