exporting xlsx files to Google drive

[GreggB]

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.

[Cynthia_sas]

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

[GreggB]

***** 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;

[RW9]

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.

[GreggB]

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.

[RW9]

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.

[GreggB]

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.

[Reeza]

What version of SAS 9.4? 

ODS Excel is also an option. 

[GreggB]

Not sure.  Is this what you mean?

SAS 9.4 TS Level 1M3

[Reeza · Accepted solution]

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

[GreggB]

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;

[Reeza]

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

[GreggB]

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

[Cynthia_sas]

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

[Reeza]

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