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

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Reeza
Super User

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

View solution in original post

18 REPLIES 18
Cynthia_sas
SAS Super FREQ
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
Pyrite | Level 9

***** 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
Diamond | Level 26 RW9
Diamond | Level 26

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
Pyrite | Level 9

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
Diamond | Level 26 RW9
Diamond | Level 26

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
Pyrite | Level 9

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
Super User

What version of SAS 9.4? 

ODS Excel is also an option. 

GreggB
Pyrite | Level 9

Not sure.  Is this what you mean?

 

SAS 9.4 TS Level 1M3

Reeza
Super User

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

GreggB
Pyrite | Level 9

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
Super User

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

GreggB
Pyrite | Level 9

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

Cynthia_sas
SAS Super FREQ

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

 

ods_excel_approach_tagattr.png

Reeza
Super User

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 18 replies
  • 5159 views
  • 3 likes
  • 4 in conversation