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. ... View more

When I look at the SAS set in the WORK library I see the labels I want for this data set. However, when I export the xlsx files, the labels are gone.  Can they be preserved? Code:   data all_exp; retain schoolname testgrade scimean scipct1 scipct2 socmean socpct1 socpct2 socpct3; set all; keep schoolname testgrade scimean scipct1 scipct2 socmean socpct1 socpct2 socpct3; label scipct1='% NM (Sci)' scipct2='% M (Sci)' scipct3='% E (Sci)' socpct1='% NM (Soc)' socpct2='% M (Soc)' socpct3='% E (Soc)' scimean='Sci Mean' socmean='Soc Mean' ; run; %macro allexp(num=); proc export data=all_exp (where=(testgrade="&num")) outfile="C:\....\Excel files\SCPASS_&yr._Gr&num..xlsx" dbms=xlsx replace; run; %mend; %allexp(num=04);%allexp(num=05);%allexp(num=06);%allexp(num=07);%allexp(num=08); ... View more

The character values in my output are left justified in each cell and the numeric values are right justified. Is there an option to center all of them?  Here's the part of the code where I list the options:   ***** DEFINE PDF FILE. *****; options nodate number pageno=1 orientation=portrait colorprinting=yes papersize=letter missing='N' nobyline bottommargin=0in topmargin=0in leftmargin=0in rightmargin=0in; ods pdf file="G:\Departments\Research\Dashboard\WorkKeys\&yr.\WorkKeys &yr. District Ranks -- &test2..pdf"; ***** PRINT REPORT. *****; proc report data=&test._ranked nowd split='*' style(header)={background=cx66cc99 font_size=12pt} style(column)={font_size=10pt} style(report)={vjust=c just=c cellpadding=0pt}; ... View more

I'm attempting to export a csv file to exactly match the vendor's requirements. Everything  is good except the last variable in the data set (sortgroup) does not export. I remember there's something different to do to ensure it gets exported but can't remember. Below is the code. I omitted several of the variables below that are being exported for brevity. (all vars are char. I'm running SAS 9.4 64 bit on a PC)   data _null_; %let _EFIERR_ = 0; /* set the ERROR detection macro variable */ %let _EFIREC_ = 0; /* clear export record count macro variable */ file 'G:\Departments\Research\TESTING\PSAT\1617\Final.csv' delimiter=',' DSD DROPOVER lrecl=32767; set WORK.final end=EFIEOD; format aicode $6. ; format PSAT8IND $1. ; format PSAT10IND $1.; . . format sortgroup $10. ; if _n_ = 1 then /* write column names */ do; put 'AI CODE' ',' 'PSAT 8/9 TEST ADMINISTRATION INDICATOR' ',' . . . 'SAT TEST CENTER' ',' 'SORT GROUP' ; end; do; EFIOUT + 1; put aicode $ @; . . . put testcenter $ @; put sortgroup $ ; ; end; if _ERROR_ then call symputx('_EFIERR_',1); if EFIEOD then call symputx('_EFIREC_',EFIOUT); run; ... View more

The date I have for DOB in a file I imported looks like this: 12/11/2000  (my proc contents says that the FORMAT and INFORMAT is numeric).   I need to convert DOB values to that 12/11/2000 becomes 12112000. When I use the code below and run a PROC FREQ the data displays as numeric values in the output.   data student2 (rename=(dob2=dob)); *ASSIGN LENGTH OF REQUIRED VARS*; length last_name $35 first_name $20 middle_name $1 ; set student; *DOB*; dob2=COMPRESS(dob,"/"); drop dob; run; ... View more