topic Re: exporting xlsx files to Google drive in SAS Programming

exporting xlsx files to Google drive

GreggB — Wed, 12 Oct 2016 14:08:55 GMT

I'm running SAS 9.4 on a PC with windows 8.1. I regularly need to export xlsx files to several shared folders and I use a macro to do this.

Problem: if I export an xlsx file, it can't be opened with Google sheets. If I choose the download option, the error says the file is corrupt. Other coworkers have the same problem when they try to open my files. As an alternative, I export the files in csv format. These files can be opened and/or downloaded. The problem with using csv format is that the data contains student IDs which are 12 digits long. They get corrupted in the csv file. I have also tried exporting the files as xlsb but result is the same as with the xlsx files.

Can anyone point me to any documentation that addresses this issue? I've yet to find any.

Re: exporting xlsx files to Google drive

Cynthia_sas — Wed, 12 Oct 2016 14:21:41 GMT

Hi:
How are you doing the EXPORT? Using PROC EXPORT or using ODS. It says here: https://support.google.com/docs/answer/40608?hl=en that Google sheets will take an XLSX file. In that case, if you are running SAS 9.4, you might try using ODS EXCEL with TAGATTR to specify a that your student ID is text.

My guess is that something in how you create the file is not creating an XLSX file that Google respects. When you use PROC EXPORT, what DBMS= option do you use? For example if you use DBMS=XLS, but name the file with XLSX file extension, that would not result in a VALID XLSX file for import by Google sheets.

Can you clarify a bit more or show more of your code for doing the export?

cynthia

Re: exporting xlsx files to Google drive

RW9 — Wed, 12 Oct 2016 14:34:00 GMT

Hi,

Just to note, you mention that CSV files get "corrupted" on the ID field. I would suggest to check the output text file, what is generally the case is that this is an Excel "feature". CSV means Comma Separated Variable file. It is a plain text file with data delimited with commas - it is not an Excel file. Excel automatically associates itself with CSV and has a built in reader to parse CSV files, however one of the "features" of this parser is that when it thinks the data is numbers it will strip off preceding 0's. This is true of most export functionality and requires some additional coding to get round, for instance with tagset output you need to specify the tagset settings so that Excel interprets the field as text.

Is there any reason to want to use Excel? In almost all outputs I would prefer to use PDF for reports, then tend to remain pretty stable across platforms/readers. For data transfer the CSV should operate as expect. If you really require and Excel file then post your export code.

Re: exporting xlsx files to Google drive

GreggB — Wed, 12 Oct 2016 14:34:14 GMT

***** GET NUMBER OF LEVELS/SCHOOLS. *****;
data _null_;
set schools nobs=numobs;
if _n_=1;
call symput('numschools',compress(put(numobs,15.)));
run;

***** GET DATE AS MACRO VARIABLE. *****;
data _null_;
call symput('rundate',trim(left(put(today(),worddate.))));
run;

***** PRINT FOR EACH SCHOOL. *****;
%macro schrpts;

%do schoolcnt=1 %to &numschools;

***** GET NEXT SCHOOL. *****;
data _null_;
set schools;
if _n_=&schoolcnt;
call symput('school',trim(tranwrd(school,'- Charter','')));
run;

proc export data=final (where=(school="&school"))
outfile="C:\Users\gb\Google Drive\&school.\Alt Assess\AltAssessRoster_&sysdate. - &school..xlsx" dbms=xlsx
replace;
run;
***** END SCHOOL LOOP. *****;
%end;

%mend schrpts;

%schrpts;

Re: exporting xlsx files to Google drive

GreggB — Wed, 12 Oct 2016 14:36:25 GMT

If I use pdf then the person I'm sharing it with couldn't edit it, correct? The reason for sharing the file is so others (non SAS users) can verify that the data is correct and leave any comments for me.

Re: exporting xlsx files to Google drive

GreggB — Wed, 12 Oct 2016 14:39:31 GMT

The ID field is ok until someone opens the file. Then, an ID such as 730123456789 gets changed to 730000000000.

Re: exporting xlsx files to Google drive

RW9 — Wed, 12 Oct 2016 14:45:04 GMT

Dangerous is you ask me. Giving users the option to change data and do things with it, we always lock it, and have a separate document to hold comments. Anyways, each to their own. For your problem you can try:

data _null_;
  set schools;
  call execute('ods tagsets.excelexp file="<path>\',strip(school),'.xlsx";
                proc report data=schools nowd;
                  columns id ...;
                  where school="',strip(school),'";
                  define id / "label" style={tagattr='format:text'};
                  ...;
                run;
                ods tagstes.excelxp close;');
run;

This will generate one file per school in the dataset (I assume they are unique!) using ods tagsets. Note the stipulation of tagattr to set ID to be text, otherwise you will still get that Excel removing 0's feature.

Re: exporting xlsx files to Google drive

GreggB — Wed, 12 Oct 2016 14:50:00 GMT

I'll give this a try. The actual data resides in our student info system which is inaccessible to them, but your point is well-taken. Yes, each school is unique.

Re: exporting xlsx files to Google drive

Reeza — Wed, 12 Oct 2016 15:33:35 GMT

What version of SAS 9.4?

ODS Excel is also an option.

Re: exporting xlsx files to Google drive

GreggB — Wed, 12 Oct 2016 15:48:39 GMT

Not sure. Is this what you mean?

SAS 9.4 TS Level 1M3

Re: exporting xlsx files to Google drive

Reeza — Wed, 12 Oct 2016 16:17:42 GMT

Yes. You can generate native XLSX files with ODS excel, similar to ODS Tagsets.excelxp.

Re: exporting xlsx files to Google drive

Reeza — Wed, 12 Oct 2016 16:17:42 GMT

Yes. You can generate native XLSX files with ODS excel, similar to ODS Tagsets.excelxp.

Re: exporting xlsx files to Google drive

GreggB — Wed, 12 Oct 2016 16:41:27 GMT

Thanks for putting me on the right track. I tried this (albeit a simpler version of the code) and it works well.

ods excel file="C:\Users\gbibb\Google Drive\alt.xlsx";
proc print data=final;
run;
ods excel close;
proc print;

Re: exporting xlsx files to Google drive

Cynthia_sas — Wed, 12 Oct 2016 20:16:00 GMT

If you want to "protect" the ID number, you need to explicitly tell Excel that it is text or give excel a format that Microsoft respects. See the use of TAGATTR in the program and the results below. Note how TAGATTR gives HEIGHT and WEIGHT 3 decimal places and how the START and ID numbers are not changed.

cynthia

Re: exporting xlsx files to Google drive

Reeza — Wed, 12 Oct 2016 20:16:20 GMT

@Cynthia_sas Doesn't ODS EXCEL handle some of that, so you don't have to deal with TAGATTR as much?

Re: exporting xlsx files to Google drive

Cynthia_sas — Wed, 12 Oct 2016 20:18:59 GMT

Hi, Reeza:
In my experience, ODS Excel is generally very, very good with Dates and will respect the SAS format for date/time values. I still find that I usually need TAGATTR for long numbers, leading zeroes, percents and number of decimal places.

cynthia

Re: exporting xlsx files to Google drive

GreggB — Thu, 13 Oct 2016 13:09:21 GMT

My comments about the 12-digit IDs may have been misleading. There's no problem on the SAS side. The corruption occurs when other users open the csv file. This is no longer a problem since I learned to use ODS to export an xlsx file.

Re: exporting xlsx files to Google drive

GreggB — Thu, 13 Oct 2016 13:09:58 GMT

Yes. ODS handled it with no problem.

Re: exporting xlsx files to Google drive

Cynthia_sas — Thu, 13 Oct 2016 16:49:03 GMT

Hi: Just to further clarify that I knew there was no problem on the SAS side of things. SAS doesn't really care how big a number it has to display as long as your SAS format is big enough to display the number with all the punctuation (commas, decimal points, etc). But Microsoft Excel does not always respect big numbers and past a certain size, has a tendency to replace significant digits in the number with zeroes.

Sometimes you can work around this issue by telling Microsoft that the number is a text string, but that doesn't always work. Usually, in cases where Excel does not use the SAS format, as written to the CSV file, you need to "move up" to use ODS to create more than a CSV file -- both ODS TAGSETS.EXCELXP and ODS EXCEL give you the ability (via TAGATTR) to specify a Microsoft format instruction for Excel to use when it opens the file you create. This is a key point -- you are NOT sending a SAS format with TAGATTR and ODS -- you are sending a Microsoft format.

As Reeza suggested, there are some SAS formats that the TAGSET destinations will automatically convert to Microsoft formats for you -- that was the posting exchange we had about date formats. I find the TAGATTR method of sending Microsoft formats to be very reliable, when the file is opened with Excel. I am glad to hear that Google sheets also supports the Microsoft format.

cynthia