IN SAS programming, we often require outputting the dataset in different formats like EXCEL and CSV etc and here are the five different ways to export the SAS dataset into .csv file.
Example:
data new ;
infile datalines dsd dlm=' ' missover;
input a b c d;
datalines;
3 5 1 1
4 1 . .
5 8 3 2
6 0 4 4
;
run;
By putting MISSOVER in the infile statement we are telling SAS to do not look for the data in the next lane if it runs out of the data, instead keep missing values for any remaining variables.
DSD and DLM options should be included always in the infile statement, if we include the
dlm=’ ‘ in the infile statement then SAS will put one digit for each variable even though we haven’t assigned any length to variable.
DSD option will tell SAS to consider a missing value if 2 delimiters are present side by side in any observation.
When we ran the above program in SAS, we create a SAS dataset name ‘NEW’ in the work directory and if we want to create a .csv file of dataset ‘NEW’ here are the 5 different ways to do it:
1)Here is the simplest method and least known method to create the .CSV file of a dataset; Using the DEXPORT statement.
Here is the syntax:
dm "dexport new 'H:\abccompany\client\Programs\Analysis\project1\class.csv' ";
When we submit the above code, SAS will automatically create a .CSV file in specified location path.
2) .CSV file can also be created using the PROC EXPORT procedure:
Here is the syntax:
proc export data=new
outfile=" H:\abccompany\client\Programs\Analysis\project1\class.csv ";
run;
3) By using the ODS and the Proc print we will be able to create a .CSV file of SAS dataset.
Here is the way to do it;
ods csv file= ‘H:\abccompany\client\Programs\Analysis\project1\class.csv ';
Example:
data new ;
infile datalines dsd dlm=' ' missover;
input a b c d;
datalines;
3 5 1 1
4 1 . .
5 8 3 2
6 0 4 4
;
run;
By putting MISSOVER in the infile statement we are telling SAS to do not look for the data in the next lane if it runs out of the data, instead keep missing values for any remaining variables.
DSD and DLM options should be included always in the infile statement, if we include the
dlm=’ ‘ in the infile statement then SAS will put one digit for each variable even though we haven’t assigned any length to variable.
DSD option will tell SAS to consider a missing value if 2 delimiters are present side by side in any observation.
When we ran the above program in SAS, we create a SAS dataset name ‘NEW’ in the work directory and if we want to create a .csv file of dataset ‘NEW’ here are the 5 different ways to do it:
1)Here is the simplest method and least known method to create the .CSV file of a dataset; Using the DEXPORT statement.
Here is the syntax:
dm "dexport new 'H:\abccompany\client\Programs\Analysis\project1\class.csv' ";
When we submit the above code, SAS will automatically create a .CSV file in specified location path.
2) .CSV file can also be created using the PROC EXPORT procedure:
Here is the syntax:
proc export data=new
outfile=" H:\abccompany\client\Programs\Analysis\project1\class.csv ";
run;
3) By using the ODS and the Proc print we will be able to create a .CSV file of SAS dataset.
Here is the way to do it;
ods csv file= ‘H:\abccompany\client\Programs\Analysis\project1\class.csv ';
proc print data = new noobs;
run;
ods trace on;
ods csv close;
By keeping ODS trace on; statement we are telling SAS no to print the results in the output window, because, we are only creating the .csv file.
NOOBS option is required here, because PROC PRINT by default will create a new variable called 'OBS' and since we don't require it, we should include the NOOBS option.
4) File statement can also be useful in creating the .csv file of a SAS dataset.
Here is the way to do it.
data _null_;
file " H:\abccompany\client\Programs\Analysis\project1\class.csv ";
set new;
put (_all_) (',');
run;
5)b) Another way using the file statement:
filename csv ‘H:\abccompany\client\Programs\Analysis\project1\class.csv';
data _null_;
set new ;
file csv dlm=',';
put ( _all_ ) (+0);
run;
If we don’t mention the PUT( _all_), SAS will not keep all the variables in the .CSV file and even if we include put (_all_) the log will say:
ERROR 79-322: Expecting a (.
ERROR 76-322: Syntax error, statement will be ignored.
Because SAS is expecting a second parentheses here to follow the first one immediately and if we just only put the closed parenthesis to get rid of the previous error,
put (_all_) ( ) ;
We will get another error, because SAS we haven’t set any specifications for the PUT statement in the 2nd parenthesis.
So we can use a do-nothing pointer control (+0) to be error free.
Import CSV files and create SAS data sets on the fly!
