BookmarkSubscribeRSS Feed
damAK
Calcite | Level 5

Hello All,

 

I am trying to export an empty dataset to csv.

I am able to get the .csv file with zero records.  But in the log I am getting an error.

 

0 records created in /DQ_VERIFICATION/EXP_XXXXXX_F_20180508.csv from

EXP_XXXXXX.

 

ERROR: Export unsuccessful. See SAS Log for details.

 

How to avoid the Error in log.

 

Thanks in advance!

9 REPLIES 9
Kurt_Bremser
Super User

Please post the complete log of your failed step.

 

Edit: I guess you not only have zero observations, but also no variables in your dataset. In that case, proc export won't work. And a situation like that makes no sense in my eyes.

arippy32
Calcite | Level 5

Here's a use case that makes complete sense for this.

 

You're a software tester that is creating delimited input files for testing, and one of the scenarios that require testing is an empty delimited file. As you want to make your data setup automated, you'd like SAS to allow you to export an empty delimited file without hassle, so that you don't have to create this one file manually.

 

If you export an empty data set that contains variables in the PDV, even though SAS will report the export was unsuccessful, the file will indeed be created. At least it did in my case.

Kurt_Bremser
Super User

Exporting an empty dataset that has variables does NOT lead to the "Unsuccessful" message. As proof run this:

data class;
set sashelp.class;
stop;
run;

proc export data=class file="/folders/myfolders/class.csv" dbms=csv;
run;

The "Unsuccessful" message comes only when there are no variables in the dataset (and then no file is actually written, although an existing file is deleted!); a csv without columns would be a completely empty file, or a file with a single line terminator. Such a file can be made with a data step that writes one empty line:

data _null_;
file '/folders/myfolders/test.csv';
put;
run;

or, if you omit the PUT statement, you create a file with zero bytes.

But for all this, you don't need proc export. No need for a hammer when you only need to stick a pin.

arippy32
Calcite | Level 5

Hi Kurt,

 

Just because it didn't produce an Export Unsuccessful message in the log for your example, doesn't mean it doesn't ever produce the message.

 

Maybe you mean it does not always result in the message.

 

Anyways, thanks for your data step example. That could be useful, but at least in my use case, which is what led me to this thread, being able to automate the creation of multiple delimited files, even if one of those files is empty, can lead one to have to use a hammer for the entire process.

Kurt_Bremser
Super User

As long as you have valid, although empty, datasets, PROC EXPORT will work. Only with "invalid" datasets without variables, you will have a problem. That is simply not covered in the inner workings of PROC EXPORT.

You can create a "safety valve" for this special case:

data class;
set sashelp.class;
stop;
run;

proc sql noprint;
select count(*) into :countvar
from dictionary.columns
where libname = 'WORK' and memname = 'CLASS'
;
quit;

%if &countvar
%then %do;
proc export data=class file='/folders/myfolders/class.csv' dbms=csv replace;
run;
%end;

%if not &countvar
%then %do;
data _null_;
file '/folders/myfolders/class.csv';
put;
run;
%end;

If you remove the

set sashelp.class;

from the first data step, the code will still work and create an empty file with one linefeed.

Tom
Super User Tom
Super User

There are many ways to tell if a dataset has any variables. DICTIONARY.TABLES (sashelp.vtable) is one way.

data _null_;
   set sashelp.vtable ;
   where libname='TESTDATA';
   length filename $256;
   filename=quote(cats(lowcase(memname),'.csv'));
   if nvar then call execute(catx(' ','proc export data=',catx('.',libname,nliteral(memname))
         ,'dbms=csv out=',filename,'replace;run;'));
   else call execute(catx(' ','data _null_;file',filename,';run;'));
run;
RW9
Diamond | Level 26 RW9
Diamond | Level 26

What you are saying and what you expect do not match.  CSV - Comma Separated Variable file is a text file with one row of header information, and then a row for each observation with the data separated by a comma.  Having no data means it is not a CSV, its just a text file with a line of text.  Hence why you are getting errors.  

Instead of trying to export something which does not exist, just write a text line to a text file (and don't call it CSV as its not):

data _null_;
  file "empty_file.txt";
  put "abc,def,ghi";
run;
  
Reeza
Super User

Here's an example of a macro that prints the data set if it exists and otherwise prints a message to the log. You can modify it to have each section do what you need it to do.

 

http://documentation.sas.com/?docsetId=mcrolref&docsetTarget=p011imau3tm4jen1us2a45cyenz9.htm&docset...

Tom
Super User Tom
Super User

Proc export works fine for a dataset with zero observations. It fails for a dataset that does not have any variables.

Try this program to see the difference.

data zero nothing(drop=_all_);
 set sashelp.class(obs=0);
run;

%let path=c:\downloads;
proc export data=zero file="&path/zero.csv" dbms=csv  replace ; 
run;
proc export data=nothing file="&path/nothing.csv" dbms=csv  replace ; 
run;

I expect the error is higher up in your process that created the dataset that does not have any variables. But if you did want to create a text file from that nothing dataset you could use another method to make the CSV file. For example you could use this macro:

https://github.com/sasutils/macros/blob/master/csvfile.sas

%csvfile(zero,outfile="&path/zero.csv");
%csvfile(nothing,outfile="&path/nothing.csv");

For ZERO it will create a file with only the header row. And for NOTHING it will create a file with zero bytes since it does not even have any variables to include in the header row.

 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 9 replies
  • 3023 views
  • 0 likes
  • 6 in conversation