creating an excel output: running proc sql and proc print

[Emma_at_SAS]

I have a sas_data that I would like to read in one excel file (description) with two sheets created by two procedures. 

In the first procedure, I want to read all columns (*) and rows with specific ID values, using proc sql.

In the second procedure, I want to read all rows and specific columns, using proc print.

My SAS output looks good but the excel file created by my code is empty.

My excel file is not open. The error message is below. I checked my c drive but "AppData" folder does not exist and no file there created today (all dates are older than today).

WARNING: An error occurred while deleting the temporary package files in
C:\Users\wxyz\AppData\Local\Temp\SAS Temporary
Files\_TD9272_SPHHS82-01_\_T000000004CA0D1C0.

Do you have any suggestions to fix my code?

Thanks! 

ODS EXCEL FILE="C:\path\description.xlsx"
options(embedded_titles='on' embedded_footnotes='on' sheet_interval = 'proc');
libname sql 'SAS-library';
proc sql;
title1 'checking specific rows';
select *
from sas_data
where PID like '331047145%' or
ID like '536%' or
ID like '588%' or
ID like 'EP%';
quit;
run;

proc print data=sas_data; var PID uniquerespid uniqueidforeachrespondent record; run;
ods excel close;

[ballardw]

What is the purpose of that LIBNAME statement? Since you are not using a library named SQL anywhere it is not needed, at least for the bit you show, and the syntax would have a file path not 'SAS-Library'.

The default setting in windows is to "hide" Appdata and many other folders. You need to set your Windows Explorer (or other file viewer) to show "hidden items" in the View settings.

Did you look in the C:\path folder shown? Or perhaps you need to show the actual code if you are hiding the path for some reason.

[Emma_at_SAS]

Thanks ballardw,
You are correct. I do not need that LIBNAME part.
I opened the hidden file for the error, but the system does not allow me to delete the file manually.

Other thoughts?

[Cynthia_sas · Accepted solution]

Hi:
I don't understand the need for PROC SQL, since PROC PRINT could use the SAME WHERE statement as you show in the PROC SQL.

It looks like SAS is trying to write the ODS EXCEL intermediate file into a Temporary File location based on your WARNING message. However, it is only a warning, not an ERROR. You said your SAS output was good. So if you really want to figure this out, I'd recommend these steps:
1) create 2 separate xlsx files one for the PROC SQL and one for the PROC PRINT. Can you run the 2 separate steps without the warning or does one reliably produce the warning. If so, then you have something you can report to Tech Support. If the 2 separate steps run OK separately to produce 2 separate XLSX without warnings, but the exact same code to one XLSX file produces the warning, then again, you have something you can report to Tech Support.
2) Change the PROC SQL step to PROC PRINT with a WHERE and see whether that gets rid of the WARNING.

But if you can reliably reproduce the warning with one step or the other, then that is a question for Tech Support. But, again, it's only a WARNING about deleting a temporary file. Remember that the XLSX file is not a single file structure, but is a collection of files that are in one archive named XLSX and that XLSX conforms to the Microsoft Open Office XML standard. So I believe that ODS EXCEL needs to create some intermediate files to make the final XLSX archive. If the output looks OK, but the WARNING really bothers you, then open a track with Tech Support. Otherwise, I would document that this appears to be a warning that's generated with ODS EXCEL. You could test with ODS CSV or ODS TAGSETS.EXCELXP to see whether you get a warning with either of those file structures. I would not expect any issues. ODS CSV will probably not be your choice since both reports would be in one sheet. But ODS TAGSETS.EXCELXP might not generate the WARNING.

Just some additional input.
Cynthia

[Emma_at_SAS]

Hi Cynthia,

Thank you for your suggestions.

I tried PROC PRINT instead of PROC SQL and it worked well and the excel file is perfect!

Thanks