Exporting data from SAS to Excel using tutorial with SAS Studio from OnDemand for Academics

[dkcundiffMD]

1 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;

72

I am using a SAS tutorial (https://www.youtube.com/watch?v=6fZ9a_3YPNU) to learn to export data to Excel. 

My code: 

libname Projects "/home/u43393119/Projects"; run; * a test;

libname Projects "/home/u43393119/Projects" xlsx
'C:\Users\user\Documents\MDM\Health outcomes formulas manuscripts\CVD Statistical tables and SAS codes\CVD output.xlsx';
%put My version of SAS is &sysvlong;
run;

My result is the following:

73 libname Projects "/home/u43393119/Projects";

NOTE: Libref PROJECTS was successfully assigned as follows:

Engine: V9

Physical Name: /home/u43393119/Projects

73 ! run;

74

75 libname Projects "/home/u43393119/Projects" xlsx

____

22

ERROR 22-7: Invalid option name XLSX.

76 'C:\Users\user\Documents\MDM\Health outcomes formulas manuscripts\CVD Statistical tables and SAS codes\CVD output.xlsx';

ERROR: Libref PROJECTS is not assigned.

ERROR: Error in the LIBNAME statement.

77 %put My version of SAS is &sysvlong;

My version of SAS is 9.04.01M6P110718

78 run;

What did I do wrong?

Thanks. 

David Cundiff

[Kurt_Bremser · Accepted solution]

Your code should be

libname Projects xlsx 'C:\Users\user\Documents\MDM\Health outcomes formulas manuscripts\CVD Statistical tables and SAS codes\CVD output.xlsx';

[dkcundiffMD]

That worked! Thanks. 

Now the next part is also a problem. I want to export data that I create in libname Projects, specifically a file called Projects.source. I get error messages:

Code: 

libname Projects "/home/u43393119/Projects";

proc sort data=Projects.source;
by weighted_no;
run; quit;

libname Projects xlsx
'C:\Users\user\Documents\MDM\Health outcomes formulas manuscripts
\CVD Statistical tables and SAS codes\CVD output.xlsx';
%put My version of SAS is &sysvlong;
options validvarname=v7;

data Projects.source;
set Projects.source;

run;

Log shows errors: 

Errors, Warnings, Notes

 Errors (2)

 Warnings (1)

 Notes (6)

1 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;

72

73

74 libname Projects "/home/u43393119/Projects";

NOTE: Libref PROJECTS was successfully assigned as follows:

Engine: V9

Physical Name: /home/u43393119/Projects

75

76 proc sort data=Projects.source;

77 by weighted_no;

78 run;

NOTE: Input data set is already sorted, no sorting done.

NOTE: PROCEDURE SORT used (Total process time):

real time 0.00 seconds

user cpu time 0.00 seconds

system cpu time 0.00 seconds

memory 2456.46k

OS Memory 42128.00k

Timestamp 03/04/2021 11:58:32 PM

Step Count 272 Switch Count 0

Page Faults 0

Page Reclaims 634

Page Swaps 0

Voluntary Context Switches 4

Involuntary Context Switches 0

Block Input Operations 0

Block Output Operations 0

78 ! quit;

80

81 libname Projects xlsx

82 'C:\Users\user\Documents\MDM\Health outcomes formulas manuscripts

83 \CVD Statistical tables and SAS codes\CVD output.xlsx';

NOTE: Libref PROJECTS 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

84 %put My version of SAS is &sysvlong;

My version of SAS is 9.04.01M6P110718

85 options validvarname=v7;

86

87 run;

88

89 data Projects.source;

90 set Projects.source;

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

91

92 run;

There are no variables for the output file

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

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

WARNING: The data set PROJECTS.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.00 seconds

system cpu time 0.00 seconds

memory 2202.87k

OS Memory 42892.00k

Timestamp 03/04/2021 11:58:32 PM

Step Count 273 Switch Count 0

Page Faults 0

Page Reclaims 859

Page Swaps 0

Voluntary Context Switches 0

Involuntary Context Switches 0

Block Input Operations 0

Block Output Operations 0

93

94 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;

How do I reference my file of interest: Project.source; ?

Thanks. 

[Kurt_Bremser]

You cannot have two libraries with the same name.

[Reeza]

Are you sure your computer has access to your C drive like that? I suspect not for some reason.

To export an Excel file using libnames it would be the following:

libname myExp XLSX "/home/u43393119/Projects/sashelp_class.xlsx";

data myExp.class;
set sashelp.class;
run;

libname myExp;

Check if that works first. If it does then start playing around with different locations and data sets until you get what you want. This should work as is.

[ballardw]

This statement has several errors:

libname Projects "/home/u43393119/Projects" xlsx
'C:\Users\user\Documents\MDM\Health outcomes formulas manuscripts\CVD Statistical tables and SAS codes\CVD output.xlsx';

First is when the engine is used for a libname it comes after the name of the library. So

libname projects xlsx

is how this should start.

Next you have two locations listed, "/home/u43393119/Projects" would not be valid as an xlsx as it is not an XLSX file. So the full libname would look like:

libname projects xlsx 'C:\Users\user\Documents\MDM\Health outcomes formulas manuscripts\CVD Statistical tables and SAS codes\CVD output.xlsx' ;

RUN; is not used with Libname statements. You would use Run to end a data step or most procedures.

BTW if you attempt to change the definition of a libname while one of it's data sets is open you may have issues. It is a good idea to use:

libname projects clear;

or what ever the name is when you are done with the library or want to change the definition.

[dkcundiffMD]

I was able to create a file to export.
Many thanks.
David Cundiff