topic Re: Excel libname in SAS Programming

Excel libname

claire_sas — Fri, 25 Sep 2015 16:10:24 GMT

I'm using the Excel libname engine to output some SAS data to Excel. There are several datasets that I want to output and I have created the templates in Excel with the appropriate titles and formatting, so I am just interested in outputting the numbers. However it seems that I also need to output column headers (am I right?) and because my column headers are years (2001-2015) then when they are output to Excel it shows each header cell as having the error 'number stored as text'. I know I can turn off the error but if I send somebody the workbook they will still see the error. It will take too long to click on each one and choose ignore error, because there are so many. Does anyone know how to output the data and keep the headings as numeric?

Thanks.

Re: Excel libname

Reeza — Fri, 25 Sep 2015 16:21:45 GMT

How are your templates formatted, I would have assumed the templates would correct this if they are formatted correctly. I;m assuming you are using the LABELS option as well, rather than Header?

Re: Excel libname

claire_sas — Fri, 25 Sep 2015 16:27:50 GMT

I formatted the rows as text but it still tells me that it's a number that I am storing as text, like I don't know that already. I tried changing it to numeric but that didn't work either. Is there something else that I should be doing?

I am not using those options because the column names don't have labels, they are just 2001, 2002, etc. But if I did give them labels they would just be the same and surely they would come out as text too?

Thanks.

Re: Excel libname

Reeza — Fri, 25 Sep 2015 16:47:08 GMT

What if you format it as numbers? I would try the LABELs option. Are you using EG, usually 2001 isn't a valid column name in SAS.

Re: Excel libname

claire_sas — Mon, 28 Sep 2015 08:44:48 GMT

As mentioned I tried formatting the Excel rows as numbers and that didn't get rid of the green triangle errors.

Yes, I'm using EG and it doesn't have a problem with column headings containing only numbers.

When you say the LABELS option do you mean DBLABEL=YES? My column headers don't have labels, and I could create them but it's not really practical because there are so many columns, and aren't labels always stored as text anyway? I want SAS to recognise that the column headings are numbers rather than text, so that they are sent to Excel as numbers.