ERROR: No disk space is available for the write operation

While attempting to run a database in excess of 56gb I repeatedly received this error message "ERROR: No disk space is available for the write operation".  When working in SAS all procedures take place wherever the temp folder is located, which is true even if you reference a different library with a libname statement.  The library is the final resting ground for the data, yet all procedures are performed in that temp folder.  The problem I encountered was because my computer's hard drive was ~110 gb and the file was ~56gb.  I have a network folder that is 250gb, but first I needed to figure out how to designate that drive for my SAS temp folder.

I was unable to find a solution online, so I wanted to share my ultimate solution. 

The temp drive and folder I wished to use was "M:\SAS temp"

1.) Locate the config file called "SASV9.CFG"
1a.) My location was "C:\Program Files\SAS\SASFoundation\9.2\nls\en"

2.) Create a back-up config file, just in case.

3.) Open SASV9.CFG
3a.) Under the warning box, about 5 lines down it states
        /*setup the default SAS System user work folder          */
3b.) You will want to change this path to wherever you want your new temp file, so it should read:

/*setup the default SAS System user work folder                 */
-WORK "M:\SAS temp"

4.) Save and close this file

5.) Open sas.exe in "C:\Program Files\SAS\SASFoundation\9.2"


The problem should be solved and all temporary files, including the work library, will now be saved in this location. 

A final note, network drives may run significantly slower.  Therefore, it may be wise to switch back your temp folder once you have completed running this larger file.

Good Luck!

ODS graphics error

ERROR: Unable to load the Java Virtual Machine
Today I received this error while attempting to execute ODS graphics.  I was using SAS 9.2 on a windows XP platform.  In my prior life with another agency this problem has occurred when I upgraded from 9.1 to 9.2, so this must be a common issue.  Speaking with the local IT help desk and a SAS associate proved useless, yet the problem was solved.

The issue is an incorrect pathway present for your Java Virtual Machine.  You will need admin rights to fix this error, but once you access the files, it is quite easy.

1) Open the folder: C:\Program Files\SAS\SASFoundation\9.2\nls\en
  a) in this folder you will find a file called SASV9.CFG.
  b) make a copy of this folder called something else, i.e. SASv9_backup, just in case.

2) Open the program file containing your java files: C:\Program Files\Java

3) Open the file SASV9.CFG
   a) Scroll down until you hit the line that says:
        -JREOPTIONS=(-Dsas.jre.libjvm=C:\PROGRA~1\Java\JRE15~1.0_1\bin\client\jvm.dll
   b) This is the path SAS is taking to find your version of Java Virtual Machine.
   c) Return to the JAVA folder and check if that pathway exists, it probably won't.
   d) If you have the folder "JRE1.5.0_12" in the java folder then make a simple change to the pathway
   --JREOPTIONS=(-Dsas.jre.libjvm=C:\PROGRA~1\Java\jre15~1.0_1\bin\server\jvm.dll

 4) This is where I got hung up!
   a) Open SAS through the ORIGINAL pathway, not your normal shortcut.  The normal pathway is:
       C:\Program Files\SAS\SASFoundation\9.2\SAS.exe

5) Check to see if the fix worked by running:

%put JREoptions: %sysfunc(getoption(JREOPTIONS));
PROC JavaInfo;

If it runs the procedure with no errors, ODS graphics is ready to execute.


FIX 2.

For the second fix we will adjust the JREOPTIONS in the file SAS utilizes prior to going to the other SASv9.cfg we attempted to change.

1) Open C:\Program Files\SAS\SASFoundation\9.2

2) Locate SASV9.CFG, and make a back up

3) Open this file and under the first command line that states:

-CONFIG "C:\Program Files\SAS\SASFoundation\9.2\nls\en\SASV9.CFG"

Add a second command line:

-JREOPTIONS=(-Dsas.jre.libjvm=C:\PROGRA~1\Java\JRE6\bin\client\jvm.dll -Djava.security.policy=!SASROOT\core\sasmisc\sas.policy -Dsas.ext.config=!SASROOT\core\sasmisc\sas.java.ext.config -Dsas.app.class.path=C:\PROGRA~1\SAS\SASVER~1\9.2\eclipse\plugins\tkjava.jar -DPFS_TEMPLATE=!SASROOT\core\sasmisc\qrpfstpt.xml -Djava.class.path=C:\PROGRA~1\SAS\SASVER~1\9.2\eclipse\plugins\SASLAU~1.JAR -Djava.system.class.loader=com.sas.app.AppClassLoader -Xmx128m -Xms128m -Djava.security.auth.login.config=!SASROOT\core\sasmisc\sas.login.config -Dtkj.app.launch.config=!SASROOT\picklist)

NOTE!  This whole code needs to be in one line.

This fix will work if in the JAVA folder you have the folder "JRE6". 

Run the check on SAS:

%put JREoptions: %sysfunc(getoption(JREOPTIONS));
PROC JavaInfo;

If this PROC runs without an error, ODS graphics is fixed.

 Why this works.

You will notice that the pathway SAS originally uses to identify Java Virtual Machine (Java\JRE15~1.0_1\bin\client\jvm.dll) doesn't exist.  SAS needs to find the program "jvm.dll" and so we must force SAS to find that program.  By fixing the pathway we allow SAS to locate the necessary program to run ODS Graphics, thus solving the problem.

This was a major pain today and just wish to share the fix.

Exporting

I have discussed how to import data into SAS from SPSS and Excel files, but what about when a user wishes to get the data out of SAS into another file format.  Exporting data is one of those times I really love SAS and the tools/wizards they provide.   Let’s start with the SAS Export Wizard.

The Export wizard:

                File -> Export Data…

Here you can see the Export Wizard is open and ready for you to select the file.  In the Library drop down menu, you simply select the library location, and the “member” is the file name.  In this example I would be exporting the SAS dataset “DATA” from the “WORK” library.

One thing you may quickly notice is the extensive list of file formats available to you by SAS.  A few include:  Excel, comma/tab delimited, JMP, SPSS, ect.  That is right SPSS!  I find it interesting that SAS will let users export straight into SPSS, and it works, but you can’t export an SPSS file into SAS without issues.

The final step after selecting the file format is to find the final folder destination of the data.  Once the destination is selected, you will assign a table name and hit FINISH, which will complete your export.

Simple, right? 

*************************************************************************************            

Option 2.  SAS Macro

When explaining how to import Excel files, I discussed the positives and negatives of using these macros for importing/exporting.  The only reason I use these macros is when importing the data, making quick changes, and exporting the data.  I won’t utilize this macro for my day to day exporting, but if I’m making summary variables or mean/mode/median imputations, this is the easiest way.

%macro expo (data=);

PROC EXPORT DATA= WORK.data

            OUTFILE= "C:\folder\datasets\Updated\&data"

            DBMS=EXCEL LABEL REPLACE;

     NEWFILE=YES;

RUN;

%mend;

*The first line creates a macro called “expo” where we are going to use the macro to define “data”.

The macro utilizes PROC EXPORT and states the data is in the work library and is called “data”.  Second note, I created a new file folder called “Updated”, so the data didn’t just overwrite the old data in the folder from where you imported the data.

DBMS=Excel, tells SAS it will be exporting the data as an Excel file with the labels printed instead of the names. ;

%mend;

*closes the macro.;

*The final step is calling the macro;

%expo(data=file);

This statement exports the SAS dataset as “file” into the folder on your drive specified in the macro statement.

These are just two of the forms of exporting data I use most often, but again, there are many ways to export. 

Cheers!

Excel Import

There are many options when importing Excel files, but today I will go over the three options I most commonly utilize.  These three options will move from easiest to hardest, but this doesn’t mean least to most useful.  The real benefit of SAS over some other programs is the flexibility it provides in all aspects of data management, including simple data importation.

The three options:

1.)    Import excel through SAS’s import wizard.

2.)    Import excel through code

3.)    Import excel through a macro

****************************************************************************

OPTION 1:

When importing excel through SAS’s import wizard I would always have the excel file saved in the ’97 – 2003 format, SAS hasn’t quite caught up to Excel 2007. 

Once in SAS, go to File-> Import Data… 

This opens the Import Wizard.  The default is “Standard Data Source” with “Microsoft Excel 97/2000/2002/2003 Workbook” in the drop down menu, click “Next” once you verify everything is correct.

 Hit “Browse…” to find the dataset you’re looking to import and click “OK”.

The next screen asks you to select the table you are importing.  This is the table in the original excel file where your data is saved.  Click “Next” once you have selected the correct table.

The final screen asks you to select the location you want to place the excel sheet once imported.  The default library is “WORK”.  The second drop down menu is “Member:”, here SAS is asking you to name the new SAS dataset, anything will do here, I prefer simple names like “d”, “data”, “a”, ect.  Once named you can select “Finish” and you are completed.

You will see in the SAS log “NOTE: WORK.D data set was successfully created. “  Now your file can be located in the Work Library, and is ready for manipulation/analysis.

LIMITATIONS:  The import wizard can’t handle variables where the values are mixture of numbers and characters.  It assigns the variable type for the column based on the first few rows.  You will run into problems if you have mixed variables, so please check your data after importation, or use the next option.

OPTION 2:

Option two saves out a permanent SAS dataset, but require code. 

Before you begin to write the code, the excel file *MUST* be saved as an Excel ’95 document.  This can be done in Excel through file->Save as ->Other formats, and place the “save as type” as “Microsoft Excel 5.0/95 Workbook”.

The first SAS code is to create a new library with a libname statement.

libname b "C:\folder\datasets";

*This creates a library named "b" where all files are saved into C:\folder\datasets; remember this is just an example, but the location your saving the data must already exist;

proc access dbms=xls;

   create b.xlsa.access;

   path = 'C:\folder\datasets\excelfile.xls';

   scantype  = yes;

   worksheet = deploy;

   getnames  = yes;

   assign    = yes;

   create b.xlsv.view;

   select all;

run;

*Some notes:

dbms=xls, is what tell SAS it is an excel file.

create=b.xlsa.access, creates your permanent dataset "xlsa.access" in the "b" library

path= 'C:\folder\datasets\excelfile.xls', is the exact location of the excel file.

scantype= no, this allows a column to not be assigned numeric if most entries are numeric.

mixed=yes, this allows columns with numbers and characters to be formatted as character.

worksheet=sheet1, is the name of the worksheet the data can be found in the excel file.

getnames=yes, SAS takes the first row from the excel file and uses it to write column names.

assign=yes, tells SAS to name the columns legal SAS names using excel column names

create b.xlsv.view,  creates a subfolder called xlsv, which we delete later.

select all, tells SAS all observations will be available to view.

;

data b.SASdataset;

      set b.xlsv;

run;

*This data step saves the imported excel file "b.xlsv" into "b.SASdataset", which is located in the library "b" and named SASdataset.  The dataset can now be found at "C:\folder\datasets\SASdataset.sas7bdat";

proc datasets library=b;

      delete xlsv/memtype=view;

      delete xlsa/memtype=access;

run;quit;

*This final data step deletes your temporary folders xlsv and xlsa that were created in the PROC ACCESS step;

LIMITATIONS:  This procedure has few/no limitations, once you write the code once, it can be re-used and customized to fit the file you’re importing.  It is the most common way I import files.

Option 3:

This final option is through writing a SAS macro, it is the most challenging but very useful.  I usually use this option when I am importing a dataset, making quick changes, i.e. imputation, and then exporting it right out as an excel file. 

%macro impo(data=);

 PROC IMPORT OUT= WORK.Data

            DATAFILE= "C:\folder\datasets\&data"

            DBMS=EXCEL REPLACE;

     SHEET="&data";

     GETNAMES=YES;

     MIXED=NO;

     SCANTEXT=YES;

     USEDATE=YES;

     SCANTIME=YES;

RUN;

%mend;

*You are writing a macro called impo by using the proc import option.  The file is imported into your work library so nothing needs to be done for renaming the file.

DATAFILE= "C:\folder\datasets\&data", tells     the location of your excel file.

SHEET="&data", tells SAS the sheet will be named when you call the macro. 

NOTE: The sheet and excel file must be named the same.

All of the other commands should be familiar.

The code is ended with the %mend to tell SAS the macro is completed.;

%impo(data=dataset);

*Here you are calling the macro you just created and saying the excel database is called "dataset".  Once these two steps are run, the sheet has been imported.;

This option is extremely useful when importing numerous datasets all in the same SAS program.  I use this primarily when doing quick steps, i.e. imputation, summary variables, ect.  Very useful when used for the right reasons, otherwise unnecessary steps.

LIMITATIONS:  writing a SAS macro takes a few extra steps, but any extra steps can create errors.  This is time consuming and if used for the wrong reasons, just unnecessary. 

There are many ways to import Excel files, but I just wanted to show the ones I use the most, and believe the most useful.  However, if you ask someone else, I’m sure they will mention other options.

Cheers!

Import from SPSS

One of the most common importation question asked is how to import SPSS files into SAS.  My longterm plan is to provide a quick guide on how to import every file format into SAS.  However, SPSS is by far the most frequently asked format, so it will be explained first.

Two excellent options exist:

1.) Through a program, like Stat-transfer

2.) Via an SPSS portable file straight into SAS.
*********************************************
Option 1, Stat-transfer is an excellent program for a simple conversion from an SPSS file into a SAS file.  It can be used for either an SPSS portable file (.por) or a normal SPSS data file (.sav).  This program does cost money, so it isn't a preferred option, unless you need to transfer a variety of file formats into SAS files (i.e. Access, ascii, excel, matlab, minitab, s-plus, STATA, ect.).  Stat-transfer is the only transfer program I have used.  It has always worked great, so I would recommend this program if you frequently need to import files into SAS from a variety of data formats.

Option 2, Import a portable SPSS file directly into SAS.

The first step is save the SPSS data file as a portable file (.por).

1.) Open the file in SPSS.
2.) Go to File -> Save As...
               A new window will open, you will type in the new file name and in the "Save as type" drop down            menu, choose "Portable (*.por)".  This will save the file into the same folder as the original
SPSS file unless otherwise stated.

***Note: You will see the option of saving the file as "SAS v7+ Windows long extention (*.sas7bdat).  This is the normal SAS file extension, but this will often not import into SAS properly.

Now your file is saved as a portable SPSS file, lets get it into SAS.
*****************************************************************************
libname a spss 'd:\SAS Folder\spssfile.por';

*ANNOTATION: With the libname statement we are creating a new library to import SPSS. In this example I am calling the SAS library "a". This is followed by the SPSS command.  Lastly, the file location, including the .por extension;                      

libname b 'C:\SAS Folder';

*ANNOTATION: Now you have imported the file into library "a", which can be found in the explorer tab. If you open the library you see the file as "_first_"  however, this file contains errors. So we want to save this into a permanent SAS dataset, so we create a new SAS library with the libname statement;                                                 

data b.data;

set a._first_;

run;

*ANNOTATION: Finally, we are saving the SPSS file "a._first_" into a permanent SAS file in the "b" library with the file name "data". Now you will be able to see your SAS dataset in the file-folder “C:\SAS Folder” as "data.sas7bdat";       

*****************************************************************************

If you follow these steps the file will be converted from an SPSS dataset into a working SAS dataset, which you may now use for any analysis.    

                                                            

About

Today I was driving home from work while contemplating the two-hour conversation I had with my colleague prior to leaving.  The conversation involved a detailed explanation on a variety of ways to import an SPSS file into SAS for analysis.  This conversation has occurred on multiple occasions with a variety of people, which made me contemplate the original hours it took me to learn these now routine procedures.  When I attempted to learn these skills, I found guides and information, but nothing that was crystal clear.  However, over the years, I have honed my skills on ways to annotate and explain these procedures so that any of my colleagues could follow the steps for their own projects.

This guide will begin by explaining some of the most common questions I have received, and I will be providing multiple solutions to the same problem, from a beginners prospective.  I will guide you from the explanation of the problem to the final solution.  This guide is here to help others, so please don’t hesitate to comment or ask specific questions, if you have this problem other people probably have as well.  I look forward to providing these tips for all future SAS experts.

Cheers!  ~David