Excel and SAS GRID

[SR2019]

Hello All,

Is there an easy solution to read in an existing excel and add few columns to that excel in SAS GRID?

Thank You,

SR.

[Kurt_Bremser]

There's a variety of methods to get data from Excel into SAS, and then all options to manipulate data that SAS provides are open.

Afterwards, re-export to Excel in form of pure or even formatted data is possible.

It all depends on what you have, and what you want to get out of it.

[Ksharp]

I do't know if the following code could work.

libname x xlsx 'c:\temp\have.xlsx' ;
proc sql;
alter table x.'Sheet1$'n
 add new_variable char(20);
quit;

[DWilson]

---

@Ksharp wrote:

I do't know if the following code could work.

libname x xlsx 'c:\temp\have.xlsx' ;
proc sql;
alter table x.'Sheet1$'n
 add new_variable char(20);
quit;

---

That doesn't work 🙂

[DWilson]

---

@SR2019 wrote:

Hello All,

Is there an easy solution to read in an existing excel and add few columns to that excel in SAS GRID?

Thank You,

SR.

---

I'm not sure what you consider easy. This is how I would do it:

1) Read contents of excel file into SAS data set

2) add new variables to SAS data set to represent new columns you want

3) Write out the SAS data set back to Excel, overwriting the existing excel file

One drawback to this is that you are overwriting the excel file so you'll lose all the nice formatting and what not included in your excel file.

If you are running SAS on a windows PC I think you could create a simple VBSCRIPT that you could run to add columns to excel and preserve the formatting.

[ErikLund_Jensen]

Hi @SR2019 

Linux SAS cannot handle native Excel file formats, only xlsx-files, and there is no OLE support. But if your spreadsheet is xlsx, you can read xlsx files with proc import or the xlsx libname engine and write them proc export, the xlsx libname engine or ODS the same way as in windows. There is no difference.

Usually, the difficult part is to access the files, because spreadsheets normally resides on windows machines in places where the linux system can't see them, so it is necessary to FTP them to linux or move them to a windows folder, that is mounted on the linux system. 

If it is not meant for production there is also SAS Enterprise Guide, and for production work there is a SAS PC files server that might make things a little easier, but I have no experience with it and don't miss it, because both FTP and mount works well for us, at least as well as the match between SAS and Excel usually works. These two are not meant for each other!