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.
Yes. You can generate native XLSX files with ODS excel, similar to ODS Tagsets.excelxp.
***** 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;
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.
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.
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.
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.
What version of SAS 9.4?
ODS Excel is also an option.
Not sure. Is this what you mean?
SAS 9.4 TS Level 1M3
Yes. You can generate native XLSX files with ODS excel, similar to ODS Tagsets.excelxp.
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;
Yes. You can generate native XLSX files with ODS excel, similar to ODS Tagsets.excelxp.
The ID field is ok until someone opens the file. Then, an ID such as 730123456789 gets changed to 730000000000.
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
@Cynthia_sas Doesn't ODS EXCEL handle some of that, so you don't have to deal with TAGATTR as much?
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.