Re: Exporting SAS data to Excel--It prints out but doesn't go to Excel

dkcundiffMD · Posted 03-05-2021 02:49 PM

Hi,

I use SAS OnDemand for Academics.

In following the tutorial about exporting data to Excel it printed out but didn't arrive in Excel. I tried three different Excel locations. The empty CVD ouput.xlsx file is ready.

Code

libname x xlsx
'C:\Users\user\OneDrive\IHME GBD work\CVD v diet RFs paper tables and SAS codes\CVD ouput.xlsx';
%put My version of SAS is &sysvlong;
options validvarname=v7;

data x;
set Projects.source;
keep weighted_no
sex_ID
pmeat17KC
rmeat17KC
fish17KC
milk17KC
poultry16KC
eggs16KC
SFA16KC
PUFA17KC
TFA17KC
;
run;

proc print data=x;
run;

Results:


Obs	sex_id	weighted_no	Pmeat17KC	Rmeat17KC	Fish17KC	Milk17KC	Poultry16KC	Eggs16KC	SFA16KC	PUFA17KC	TFA17KC
1	1	1	2.4652	93.121	3.628	8.226	30.343	31.9542	182.576	72.481	5.5570
2	2	2	1.8631	61.095	3.102	7.806	30.343	31.9542	183.016	70.471	6.7463
3	1	3	2.4652	93.121	3.628	8.226	30.343	31.9542	182.576	72.481	5.5570
4	2	4	1.8631	61.095	3.102	7.806	30.343	31.9542	183.016	70.471	6.7463
5	1	5	2.4652	93.121	3.628	8.226	30.343	31.9542	182.576	72.481	5.5570
6	2	6	1.8631	61.095	3.102	7.806	30.343	31.9542	183.016	70.471	6.7463
7	1	7	2.4652	93.121	3.628	8.226	30.343	31.9542	182.576	72.481	5.5

libname x clear;

dkcundiffMD · Posted 03-05-2021 02:54 PM

Also the log:
Warnings
Notes (7)

1 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;
72
73 libname x xlsx
74 'C:\Users\user\OneDrive\IHME GBD work\CVD v diet RFs paper tables and SAS codes\CVD ouput.xlsx';
NOTE: Libref X was successfully assigned as follows:
Engine: XLSX
Physical Name: C:\Users\user\OneDrive\IHME GBD work\CVD v diet RFs paper tables and SAS codes\CVD ouput.xlsx
75 %put My version of SAS is &sysvlong;
My version of SAS is 9.04.01M6P110718
76 options validvarname=v7;
77
78 data x;
79 set Projects.source;
80 keep weighted_no
81 sex_ID
82 pmeat17KC
83 rmeat17KC
84 fish17KC
85 milk17KC
86 poultry16KC
87 eggs16KC
88 SFA16KC
89 PUFA17KC
90 TFA17KC
91 ;
92 run;

NOTE: There were 7886 observations read from the data set PROJECTS.SOURCE.
NOTE: The data set WORK.X has 7886 observations and 11 variables.
NOTE: DATA statement used (Total process time):
real time 0.02 seconds
user cpu time 0.01 seconds
system cpu time 0.02 seconds
memory 3558.87k
OS Memory 45480.00k
Timestamp 03/05/2021 07:38:25 PM
Step Count 487 Switch Count 5
Page Faults 0
Page Reclaims 906
Page Swaps 0
Voluntary Context Switches 23
Involuntary Context Switches 0
Block Input Operations 0
Block Output Operations 1544

93
94 proc print data=x;
95 run;

NOTE: There were 7886 observations read from the data set WORK.X.
NOTE: PROCEDURE PRINT used (Total process time):
real time 21.97 seconds
user cpu time 21.77 seconds
system cpu time 0.16 seconds
memory 123748.00k
OS Memory 178596.00k
Timestamp 03/05/2021 07:38:47 PM
Step Count 488 Switch Count 0
Page Faults 0
Page Reclaims 65272
Page Swaps 0
Voluntary Context Switches 136
Involuntary Context Switches 24
Block Input Operations 0
Block Output Operations 12136

96
97 libname x clear;
NOTE: Libref X has been deassigned.
98
99 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;

ballardw · Posted 03-05-2021 03:42 PM

To have the output from a procedure like Proc Print you use ODS information to provide the output file name

Ods excel file="<some path>/procprintoutput.xlsx";

proc print data=somedateset;
run;
ods excel close; /*import, this finishes the output*/

I also think you may be misunderstanding the library concept. Libraries in SAS store data sets and catalogs, which are special and more than a bit complicated composite storage locations, generally not output from reporting and analysis procedures.

You can send the output from multiple procedures to the same output destination by placing the procedures that generate the output inside the same ODS "sandwich", an ODS destination/ ods destination close; pair of statements. Depending on the specific destination there are options to control which "page" or "sheet" output goes to.

dkcundiffMD · Posted 03-08-2021 01:35 PM

It didn't work because of something about the physical location of the output file. It was the same physical location that was ok before.

libname x xlsx 'C:\Users\user\Documents\MDM\Health outcomes formulas manuscripts\CVD Statistical tables and SAS codes/CVD output.xlsx';
data x; 
set Projects.source;
keep sex_ID weighted_no pmeat17KC rmeat17KC fish17KC milk17KC poultry16KC 
eggs16KC SFA16KC PUFA17KC TFA17KC;
run;

proc print data=x;
run;

Ods excel file="<C:\Users\user\Documents\MDM\Health outcomes formulas manuscripts\CVD Statistical tables and SAS codes/CVD output.xlsx";

proc print data=x;
run;
ods excel close; /*import, this finishes the output*/

Log
 1          OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;
 NOTE: ODS statements in the SAS Studio environment may disable some output features.
 73         
 74         libname x xlsx 'C:\Users\user\Documents\MDM\Health outcomes formulas manuscripts\CVD Statistical tables and SAS codes/CVD
 74       !  output.xlsx';
 NOTE: Libref X was successfully assigned as follows: 
       Engine:        XLSX 
       Physical Name: C:\Users\user\Documents\MDM\Health outcomes formulas manuscripts\CVD Statistical tables and SAS codes/CVD 
       output.xlsx
 75         data x;
 76         set Projects.source;
 77         keep sex_ID weighted_no pmeat17KC rmeat17KC fish17KC milk17KC poultry16KC
 78         eggs16KC SFA16KC PUFA17KC TFA17KC;
 79         run;
 
 NOTE: There were 7886 observations read from the data set PROJECTS.SOURCE.
 NOTE: The data set WORK.X has 7886 observations and 11 variables.
 NOTE: DATA statement used (Total process time):
       real time           0.02 seconds
       user cpu time       0.01 seconds
       system cpu time     0.02 seconds
       memory              3783.03k
       OS Memory           46952.00k
       Timestamp           03/08/2021 06:23:04 PM
       Step Count                        433  Switch Count  5
       Page Faults                       0
       Page Reclaims                     1027
       Page Swaps                        0
       Voluntary Context Switches        23
       Involuntary Context Switches      0
       Block Input Operations            0
       Block Output Operations           1544
       
 
 80         
 81         proc print data=x;
 82         run;
 
 NOTE: There were 7886 observations read from the data set WORK.X.
 NOTE: PROCEDURE PRINT used (Total process time):
       real time           21.95 seconds
       user cpu time       21.77 seconds
       system cpu time     0.17 seconds
       memory              123808.12k
       OS Memory           180068.00k
       Timestamp           03/08/2021 06:23:26 PM
       Step Count                        434  Switch Count  0
       Page Faults                       0
       Page Reclaims                     72519
       Page Swaps                        0
       Voluntary Context Switches        4
       Involuntary Context Switches      35
       Block Input Operations            0
       Block Output Operations           12128
       
 
 83         
 84         Ods excel file="<C:\Users\user\Documents\MDM\Health outcomes formulas manuscripts\CVD Statistical tables and SAS
 84       ! codes/CVD output.xlsx";
 85         
 86         proc print data=x;
 87         run;
 
 NOTE: There were 7886 observations read from the data set WORK.X.
 NOTE: PROCEDURE PRINT used (Total process time):
       real time           47.88 seconds
       user cpu time       47.23 seconds
       system cpu time     0.66 seconds
       memory              523083.93k
       OS Memory           649096.00k
       Timestamp           03/08/2021 06:24:14 PM
       Step Count                        435  Switch Count  0
       Page Faults                       0
       Page Reclaims                     351747
       Page Swaps                        0
       Voluntary Context Switches        5
       Involuntary Context Switches      71
       Block Input Operations            0
       Block Output Operations           21248
       
 
 88         ods excel close; /*import, this finishes the output*/
 ERROR: Physical file does not exist, /pbr/biconfig/940/Lev1/SASApp/<C:\Users\user\Documents\MDM\Health outcomes formulas 
        manuscripts\CVD Statistical tables and SAS codes/CVD output.xlsx.
 89

Reeza · Posted 03-08-2021 01:56 PM

You're mixing methods here when I don't think you quite understand yet, how one works.

LIBNAME method:

%let work_path=%sysfunc(pathname(work));

*create my excel file to store output;
libname demo xlsx "&work_path./Demo_ODS_EXCEL.xlsx";

*save class data set to DEMO library in a table called class;
data demo.class;
*input data set name;
set sashelp.class;
run;

*close library;
libname demo;

Or the ODS EXCEL version:

ods excel file="&work_path./DEMO_ODS_EXCEL.xlsx";

proc print data=sashelp.class;
run;

ods excel close;

Both of these work as is, tested on SAS 9.4M6, without any changes and generate two Excel files for you in the WORK library.

It appears that SAS is running on a server for you. This means that you cannot save files to your C drive or local network folders. You'll need to talk to your IT or colleagues to understand where you can save files, that's not a question we'll be able to answer for you, as we don't have the understanding of how your system is set up.

dkcundiffMD · Posted 03-09-2021 10:55 PM

My dataset is Projects.source. My variables of interest are LDLc17, sex_ID and weighted_no. I don't know how these data go with %let work.path=%sysfunc(pathname(work));

I got it to export the variables of interest only in the following way:

libname x xlsx 'C:\Users\user\Documents\MDM\Health outcomes formulas manuscripts\CVD Statistical tables and SAS codes/CVDoutput.xlsx';
 options validvarname=v7;
 
 data x;
 set Projects.source;
 Keep sex_ID weighted_no LDLC17
run;

proc print data=x;
run;

proc export data=x
   outfile='C:\Users\user\Documents\MDM\Health outcomes formulas manuscripts\CVD Statistical tables and SAS codes\CVDoutput.xlsx'
dbms=xlsx replace;

run;

log: 

 73         libname x xlsx 'C:\Users\user\Documents\MDM\Health outcomes formulas manuscripts\CVD Statistical tables and SAS
 73       ! codes/CVDoutput.xlsx';
 NOTE: Libref X was successfully assigned as follows: 
       Engine:        XLSX 
       Physical Name: C:\Users\user\Documents\MDM\Health outcomes formulas manuscripts\CVD Statistical tables and SAS 
       codes/CVDoutput.xlsx
 74          options validvarname=v7;
 75         
 76          data x;
 77          set Projects.source;
 78          Keep sex_ID weighted_no LDLC17
 79         run;
 80         
 
 WARNING: The variable run in the DROP, KEEP, or RENAME list has never been referenced.
 NOTE: There were 7886 observations read from the data set PROJECTS.SOURCE.
 NOTE: The data set WORK.X has 7886 observations and 3 variables.
 NOTE: DATA statement used (Total process time):
       real time           0.02 seconds
       user cpu time       0.01 seconds
       system cpu time     0.01 seconds
       memory              3249.15k
       OS Memory           42168.00k
       Timestamp           03/10/2021 03:35:32 AM
       Step Count                        270  Switch Count  5
       Page Faults                       0
       Page Reclaims                     815
       Page Swaps                        0
       Voluntary Context Switches        26
       Involuntary Context Switches      0
       Block Input Operations            0
       Block Output Operations           520
       
 
 81         proc print data=x;
 82         run;
 
 NOTE: There were 7886 observations read from the data set WORK.X.
 NOTE: PROCEDURE PRINT used (Total process time):
       real time           8.39 seconds
       user cpu time       8.33 seconds
       system cpu time     0.07 seconds
       memory              45520.65k
       OS Memory           88500.00k
       Timestamp           03/10/2021 03:35:41 AM
       Step Count                        271  Switch Count  0
       Page Faults                       0
       Page Reclaims                     25218
       Page Swaps                        0
       Voluntary Context Switches        3
       Involuntary Context Switches      14
       Block Input Operations            0
       Block Output Operations           4320
       
 
 83         
 84         proc export data=x
 85            outfile='C:\Users\user\Documents\MDM\Health outcomes formulas manuscripts\CVD Statistical tables and SAS
 85       ! codes\CVDoutput.xlsx'
 86         dbms=xlsx replace;
 87         
 88         run;
 
 ERROR: Temporary file for XLSX file can not be created -> /pbr/biconfig/940/Lev1/SASApp/C:\Users\user\Documents\MDM\Health outcomes 
 formulas manuscripts\CVD Statistical tables and SAS codes\/CVDoutput.$$1.  Make sure the path name is correct and that you have 
 write permission.
 
 NOTE: The SAS System stopped processing this step because of errors.
 NOTE: PROCEDURE EXPORT used (Total process time):
       real time           0.00 seconds
       user cpu time       0.00 seconds
       system cpu time     0.01 seconds
       memory              2075.40k
       OS Memory           44652.00k
       Timestamp           03/10/2021 03:35:41 AM
       Step Count                        272  Switch Count  0
       Page Faults                       0
       Page Reclaims                     550
       Page Swaps                        0
       Voluntary Context Switches        1
       Involuntary Context Switches      0
       Block Input Operations            0
       Block Output Operations           8
       
Results:

Obs	sex_id	weighted_no	LDLc17
1	1	1	2.43536
2	2	2	2.43617
3	1	3	2.43536
4	2	4	2.43617
5	1	5	2.43536
6	2	6	2.43617
7	1	7	2.43536
8	2	8	2.43617
9	1	9	2.43536
10	2	10	2.4361

Then I went to Libraries and found library x with the 3 variables. I exported it back to Projects. Then I found it in Projects and downloaded it.

Reeza · Posted 03-05-2021 03:32 PM

You never actually assign anything to the libname x.....

data x.source;
set Projects.source;

Something like that would assign it to the libname to have it exported.

@dkcundiffMD wrote:

Hi,

I use SAS OnDemand for Academics.

In following the tutorial about exporting data to Excel it printed out but didn't arrive in Excel. I tried three different Excel locations. The empty CVD ouput.xlsx file is ready.

Code

libname x xlsx
'C:\Users\user\OneDrive\IHME GBD work\CVD v diet RFs paper tables and SAS codes\CVD ouput.xlsx';
%put My version of SAS is &sysvlong;
options validvarname=v7;

data x;
set Projects.source;
keep weighted_no
sex_ID
pmeat17KC
rmeat17KC
fish17KC
milk17KC
poultry16KC
eggs16KC
SFA16KC
PUFA17KC
TFA17KC
;
run;

proc print data=x;
run;

Results:

Obs sex_id weighted_no Pmeat17KC Rmeat17KC Fish17KC Milk17KC Poultry16KC Eggs16KC SFA16KC PUFA17KC TFA17KC

1 1 1 2.4652 93.121 3.628 8.226 30.343 31.9542 182.576 72.481 5.5570

2 2 2 1.8631 61.095 3.102 7.806 30.343 31.9542 183.016 70.471 6.7463

3 1 3 2.4652 93.121 3.628 8.226 30.343 31.9542 182.576 72.481 5.5570

4 2 4 1.8631 61.095 3.102 7.806 30.343 31.9542 183.016 70.471 6.7463

5 1 5 2.4652 93.121 3.628 8.226 30.343 31.9542 182.576 72.481 5.5570

6 2 6 1.8631 61.095 3.102 7.806 30.343 31.9542 183.016 70.471 6.7463

7 1 7 2.4652 93.121 3.628 8.226 30.343 31.9542 182.576 72.481 5.5

libname x clear;

dkcundiffMD · Posted 03-05-2021 10:27 PM

Regarding:

data x.source;
set Projects.source;

Code

libname x xlsx
'C:\Users\user\OneDrive\IHME GBD work\CVD v diet RFs paper tables and SAS codes\CVD ouput.xlsx';
%put My version of SAS is &sysvlong;
options validvarname=v7;

data x.source;
set Projects.source;
keep weighted_no
sex_ID
pmeat17KC
rmeat17KC
fish17KC
milk17KC
poultry16KC
eggs16KC
SFA16KC
PUFA17KC
TFA17KC
;
run;

Log

Errors, Warnings, Notes

Errors (1)

Warnings (1)

Notes (3)

1 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;

72

73 libname x xlsx

74 'C:\Users\user\OneDrive\IHME GBD work\CVD v diet RFs paper tables and SAS codes\CVD ouput.xlsx';

NOTE: Libref X was successfully assigned as follows:

Engine: XLSX

Physical Name: C:\Users\user\OneDrive\IHME GBD work\CVD v diet RFs paper tables and SAS codes\CVD ouput.xlsx

75 %put My version of SAS is &sysvlong;

My version of SAS is 9.04.01M6P110718

76 options validvarname=v7;

77

78 data x.source;

79 set Projects.source;

80 keep weighted_no

81 sex_ID

82 pmeat17KC

83 rmeat17KC

84 fish17KC

85 milk17KC

86 poultry16KC

87 eggs16KC

88 SFA16KC

89 PUFA17KC

90 TFA17KC

91 ;

92 run;

ERROR: Temporary file for XLSX file can not be created -> C:\Users\user\OneDrive\IHME GBD work\CVD v diet RFs paper tables and SAS

codes\/CVD ouput.$$1. Make sure the path name is correct and that you have write permission.

ERROR: File X.source.DATA does not exist.

NOTE: The SAS System stopped processing this step because of errors.

WARNING: The data set X.source was only partially opened and will not be saved.

NOTE: DATA statement used (Total process time):

real time 0.00 seconds

user cpu time 0.01 seconds

system cpu time 0.00 seconds

memory 4472.03k

OS Memory 44128.00k

Timestamp 03/06/2021 03:23:29 AM

Step Count 290 Switch Count 0

Page Faults 0

Page Reclaims 1090

Page Swaps 0

Voluntary Context Switches 5

Involuntary Context Switches 0

Block Input Operations 0

Block Output Operations 0

Without data x; instead of data x.source; it will print out but not copy to Excel.

dkcundiffMD · Posted 03-05-2021 10:49 PM

It didn't work. Using data x.source, gave me an error message:

libname x xlsx 
'C:\Users\user\OneDrive\IHME GBD work\CVD v diet RFs paper tables and SAS codes\CVD ouput.xlsx';
%put My version of SAS is &sysvlong;
options validvarname=v7;

data x;
    set Projects.source;
keep weighted_no
  sex_ID
  pmeat17KC 
  rmeat17KC 
  fish17KC
  milk17KC 
  poultry16KC 
  eggs16KC
SFA16KC 
PUFA17KC 
TFA17KC
;  
run;

Ods excel file="C:\Users\user\OneDrive\IHME GBD work\CVD v diet RFs paper tables and SAS codes\CVD ouput.xlsx';
/procprintoutput.xlsx";

proc print data=x;
run;
ods excel close; /*import, this finishes the output*/

log
 Errors (1)
 Warnings
 Notes (7)
 
 1          OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;
 NOTE: ODS statements in the SAS Studio environment may disable some output features.
 73         
 74         libname x xlsx
 75         'C:\Users\user\OneDrive\IHME GBD work\CVD v diet RFs paper tables and SAS codes\CVD ouput.xlsx';
 NOTE: Libref X was successfully assigned as follows: 
       Engine:        XLSX 
       Physical Name: C:\Users\user\OneDrive\IHME GBD work\CVD v diet RFs paper tables and SAS codes\CVD ouput.xlsx
 76         %put My version of SAS is &sysvlong;
 My version of SAS is 9.04.01M6P110718
 77         options validvarname=v7;
 78         
 79         data x;
 80             set Projects.source;
 81         keep weighted_no
 82           sex_ID
 83           pmeat17KC
 84           rmeat17KC
 85           fish17KC
 86           milk17KC
 87           poultry16KC
 88           eggs16KC
 89         SFA16KC
 90         PUFA17KC
 91         TFA17KC
 92         ;
 93         run;
 
 NOTE: There were 7886 observations read from the data set PROJECTS.SOURCE.
 NOTE: The data set WORK.X has 7886 observations and 11 variables.
 NOTE: DATA statement used (Total process time):
       real time           0.11 seconds
       user cpu time       0.03 seconds
       system cpu time     0.03 seconds
       memory              3559.46k
       OS Memory           43688.00k
       Timestamp           03/06/2021 03:42:43 AM
       Step Count                        322  Switch Count  5
       Page Faults                       0
       Page Reclaims                     538
       Page Swaps                        0
       Voluntary Context Switches        747
       Involuntary Context Switches      0
       Block Input Operations            115904
       Block Output Operations           1544
       
 
 94         
 95         Ods excel file="C:\Users\user\OneDrive\IHME GBD work\CVD v diet RFs paper tables and SAS codes\CVD ouput.xlsx';
 96         /procprintoutput.xlsx";
 97         
 98         proc print data=x;
 99         run;
 
 NOTE: There were 7886 observations read from the data set WORK.X.
 NOTE: PROCEDURE PRINT used (Total process time):
       real time           47.97 seconds
       user cpu time       47.24 seconds
       system cpu time     0.72 seconds
       memory              527794.81k
       OS Memory           646344.00k
       Timestamp           03/06/2021 03:43:31 AM
       Step Count                        323  Switch Count  0
       Page Faults                       0
       Page Reclaims                     499265
       Page Swaps                        0
       Voluntary Context Switches        14
       Involuntary Context Switches      78
       Block Input Operations            0
       Block Output Operations           21264
       
 
 100        ods excel close; /*import, this finishes the output*/
 ERROR: Physical file does not exist, /pbr/biconfig/940/Lev1/SASApp/C:\Users\user\OneDrive\IHME GBD work\CVD v diet RFs paper tables 
        and SAS codes\CVD ouput.xlsx';/procprintoutput.xlsx.
 101        
 102        OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;

Results:

Obs	sex_id	weighted_no	Pmeat17KC	Rmeat17KC	Fish17KC	Milk17KC	Poultry16KC	Eggs16KC	SFA16KC	PUFA17KC	TFA17KC
1	1	1	2.4652	93.121	3.628	8.226	30.343	31.9542	182.576	72.481	5.5570
2	2	2	1.8631	61.095	3.102	7.806	30.343	31.9542	183.016	70.471	6.7463
3	1	3	2.4652	93.121	3.628	8.226	30.343	31.9542	182.576	72.481	5.5570
4	2	4	1.8631	61.095	3.102	7.806	30.343	31.9542	183.0