topic Re: Exporting data from SAS to Excel using tutorial with SAS Studio from OnDemand for Academics in New SAS User

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

dkcundiffMD — Thu, 04 Mar 2021 19:25:18 GMT

1 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;

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;

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

____

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

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

Kurt_Bremser — Thu, 04 Mar 2021 20:00:24 GMT

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';

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

Reeza — Thu, 04 Mar 2021 20:16:20 GMT

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.

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

ballardw — Thu, 04 Mar 2021 20:10:03 GMT

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.

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

dkcundiffMD — Fri, 05 Mar 2021 00:07:37 GMT

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;

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

NOTE: Libref PROJECTS was successfully assigned as follows:

Engine: V9

Physical Name: /home/u43393119/Projects

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;

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;

87 run;

89 data Projects.source;

90 set Projects.source;

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

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

94 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;

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

Thanks.

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

dkcundiffMD — Fri, 05 Mar 2021 04:41:51 GMT

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

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

Kurt_Bremser — Fri, 05 Mar 2021 07:42:36 GMT

You cannot have two libraries with the same name.