topic Re: Dealing with Empty dataset in SAS Programming

Dealing with Empty dataset

librasonali — Thu, 24 Dec 2020 13:24:15 GMT

Hi ,

I am not able to find an approach for the below case. I hope I will get help

I have 1 dataset 'X' which has a few records whereas 2nd dataset "Y" has 0 records.
I want that the X dataset should export its record into an xslx sheet whereas for Dataset Y it should put "NOTE: no output records";

Thanks in advance !

Re: Dealing with Empty dataset

Tom — Thu, 24 Dec 2020 14:48:13 GMT

This is a common reporting request and easy to solve. Just use two steps only one of which will produce output.

ods excel file='my_report.xlsx';

proc print data=have ;
run;
data _null_;
  file print;
  if nobs=0 then put 'NOTE: no output records';
  stop;
  set have nobs=nobs;
run;

Re: Dealing with Empty dataset

librasonali — Thu, 24 Dec 2020 15:23:05 GMT

what is have here ??
how can I use X and Y ???

Re: Dealing with Empty dataset

Tom — Thu, 24 Dec 2020 15:29:36 GMT

HAVE is the name of the dataset you want to report. So if your actual dataset is named X then replace HAVE with X. If you have more than one dataset then repeat the for that dataset.

Re: Dealing with Empty dataset

librasonali — Thu, 24 Dec 2020 15:43:07 GMT

Yeah I understood that example below are my input datasets and log file : I am not getting message in my log file for "Y" 'NOTE: no output records'

/***input Datasets**/

Data X;
input Num1;
cards;
101
110
102
119
150
109
;
run;
Data Y;
input Num1;
cards;
.
.
.
.
.
.
;
run

/****************************LOG FILE **********************/

 
 1          OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;
 NOTE: ODS statements in the SAS Studio environment may disable some output features.
 73         
 74         Data X;
 75         input Num1;
 76         cards;
 
 NOTE: The data set WORK.X has 6 observations and 1 variables.
 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              778.25k
       OS Memory           41432.00k
       Timestamp           12/24/2020 03:40:24 PM
       Step Count                        251  Switch Count  2
       Page Faults                       0
       Page Reclaims                     91
       Page Swaps                        0
       Voluntary Context Switches        9
       Involuntary Context Switches      0
       Block Input Operations            0
       Block Output Operations           264
       
 
 83         ;
 84         run;
 85         Data Y;
 86         input Num1;
 87         cards;
 
 NOTE: The data set WORK.Y has 6 observations and 1 variables.
 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              778.25k
       OS Memory           41432.00k
       Timestamp           12/24/2020 03:40:24 PM
       Step Count                        252  Switch Count  2
       Page Faults                       0
       Page Reclaims                     88
       Page Swaps                        0
       Voluntary Context Switches        11
       Involuntary Context Switches      0
       Block Input Operations            0
       Block Output Operations           264
       
 
 94         ;
 95         run
 96         
 97         
 98         ods excel file='my_report.xlsx';
 99         
 100        proc print data=Y ;
 101        run;
 
 NOTE: There were 6 observations read from the data set WORK.Y.
 NOTE: PROCEDURE PRINT used (Total process time):
       real time           0.03 seconds
       user cpu time       0.03 seconds
       system cpu time     0.00 seconds
       memory              1932.00k
       OS Memory           41688.00k
       Timestamp           12/24/2020 03:40:24 PM
       Step Count                        253  Switch Count  0
       Page Faults                       0
       Page Reclaims                     79
       Page Swaps                        0
       Voluntary Context Switches        0
       Involuntary Context Switches      0
       Block Input Operations            0
       Block Output Operations           24
       
 
 102        
 103        
 104        data _null_;
 105          file print;
 106          if nobs=0 then put 'NOTE: no output records';
 107          stop;
 108          set Y nobs=nobs;
 109        run;
 
 NOTE: 0 lines were written to file PRINT.
 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              779.50k
       OS Memory           41944.00k
       Timestamp           12/24/2020 03:40:24 PM
       Step Count                        254  Switch Count  0
       Page Faults                       0
       Page Reclaims                     83
       Page Swaps                        0
       Voluntary Context Switches        0
       Involuntary Context Switches      0
       Block Input Operations            0
       Block Output Operations           0
       
 
 110        
 111        
 112        proc print data=X;
 113        run;
 
 NOTE: There were 6 observations read from the data set WORK.X.
 NOTE: PROCEDURE PRINT used (Total process time):
       real time           0.02 seconds
       user cpu time       0.02 seconds
       system cpu time     0.00 seconds
       memory              588.59k
       OS Memory           41944.00k
       Timestamp           12/24/2020 03:40:25 PM
       Step Count                        255  Switch Count  0
       Page Faults                       0
       Page Reclaims                     72
       Page Swaps                        0
       Voluntary Context Switches        0
       Involuntary Context Switches      0
       Block Input Operations            0
       Block Output Operations           16
       
 
 114        
 115        
 116        data _null_;
 117          file print;
 118          if nobs=0 then put 'NOTE: no output records';
 119          stop;
 120          set X nobs=nobs;
 121        run;
 
 NOTE: 0 lines were written to file PRINT.
 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              779.50k
       OS Memory           41944.00k
       Timestamp           12/24/2020 03:40:25 PM
       Step Count                        256  Switch Count  0
       Page Faults                       0
       Page Reclaims                     71
       Page Swaps                        0
       Voluntary Context Switches        0
       Involuntary Context Switches      0
       Block Input Operations            0
       Block Output Operations           0
       
 
 122        
 123        OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;
 135

Re: Dealing with Empty dataset

Tom — Fri, 25 Dec 2020 03:47:33 GMT

Do you mean this note?

NOTE: 0 lines were written to file PRINT.

That is what you want. Since the dataset was NOT empty your don't want to write anything into the PRINT (which you have directed to EXCEL with the ODS EXCEL statement).

Make sure to close the ODS EXECL destination when you are done making your report.

Re: Dealing with Empty dataset

librasonali — Mon, 28 Dec 2020 15:06:17 GMT

@Tom wrote:

Do you mean this note?
NOTE: 0 lines were written to file PRINT.
That is what you want. Since the dataset was NOT empty your don't want to write anything into the PRINT (which you have directed to EXCEL with the ODS EXCEL statement).

Make sure to close the ODS EXECL destination when you are done making your report.

I want that the dataset which have records with populated values should be exported into a CSV where as the Dataset which is empty should so me the message in my log file 'NOTE: no output records'.

Current Situation : In both of my cases a csv is generated which blank i.e no observations. 0 lines were written to file PRINT.

Expected : The missing data should not at all generate a CSV as it is expected to give a log message.

Please find below my code :

data have;
input cusip_id Name $10. date_issue :ddmmyy10. type $10.;
format date_issue ddmmyy10.;
datalines;
10343452 Zingas    01/04/2016 bench
10343489 Stephanie 01/04/2014 main
10343445 Olivia    01/04/2012 main
10343456 Amstel    01/04/2011 bench
10343457 KEO       01/04/2017 main
10343456 Sonali    01/04/2011 ITP
;
proc print;
run;

data want;
input cusip_id ;
datalines;
.
.
.
.
.
.
.
;

run;
/*First dataset*/
ods csv file="/home/u49388206/cert/excel/NOTEMPTY.csv";

data _NULL_;
set have ;
file print;
if nobs=0  then put 'NOTE: no output records';
stop;
set have nobs=nobs;
run;

ods csvall close; 

/*second dataset*/

ods csv file="/home/u49388206/cert/excel/EMPTY.csv";

data _NULL_;
set want ;
file print;
if nobs=0 then put 'NOTE: no output records';
stop;
set want nobs=nobs;
run;

ods csvall close;

Thanks For the help you are doing !

Re: Dealing with Empty dataset

Tom — Mon, 28 Dec 2020 15:20:13 GMT

So you don't want to make an EXCEL file at all? Just delimited text files? What do you want to make for an empty dataset? Do you want to not create any file at all? Do you want to create a file with just the column headers? Do you want to delete any existing file with that target filename?

If you are using a recent enough version of SAS you can use a %IF/%THEN/%DO construct in open code. With older versions you might need to use a macro or some other method to prevent the "export" step from running.

You can set a macro variable with the number of observations in the dataset and then decide what to do. For example like this:

data _null_;
  call symputx('nobs',nobs);
  stop;
  set want nobs=nobs;
run;
%if &nobs > 0 %then %do;
  *** Code to write the CSV file ***
%end;

Note that CSV files are easy to write without using ODS or PROC EXPORT. Here is a method that just uses two data steps. One to write the header line and one to write the data. So this will create a file with just the header row when the input dataset has zero observations.

proc transpose data=HAVE(obs=0) out=names; 
  var _all_;
run;
data _null_;
  file 'have.csv' dsd ;
  set names;
  put _name_ @@;
run;
data _null_;
  file 'have.csv' dsd mod ;
  set have;
  put (_all_) (+0);
run;

Re: Dealing with Empty dataset

librasonali — Tue, 29 Dec 2020 09:31:44 GMT

@Tom wrote:

So you don't want to make an EXCEL file at all? Just delimited text files? What do you want to make for an empty dataset? Do you want to not create any file at all? Do you want to create a file with just the column headers? Do you want to delete any existing file with that target filename?

If you are using a recent enough version of SAS you can use a %IF/%THEN/%DO construct in open code. With older versions you might need to use a macro or some other method to prevent the "export" step from running.

You can set a macro variable with the number of observations in the dataset and then decide what to do. For example like this:
data _null_;
  call symputx('nobs',nobs);
  stop;
  set want nobs=nobs;
run;
%if &nobs > 0 %then %do;
  *** Code to write the CSV file ***
%end;
Note that CSV files are easy to write without using ODS or PROC EXPORT. Here is a method that just uses two data steps. One to write the header line and one to write the data. So this will create a file with just the header row when the input dataset has zero observations.
proc transpose data=HAVE(obs=0) out=names; 
  var _all_;
run;
data _null_;
  file 'have.csv' dsd ;
  set names;
  put _name_ @@;
run;
data _null_;
  file 'have.csv' dsd mod ;
  set have;
  put (_all_) (+0);
run;

What do you want to make for an empty dataset? --- I want to put message in log " NOTE: empty dataset" instead of generating a CSV file or an excel file.

Currently, in both the cases a csv is generated.