topic Re: How to delete empty datasets before exporting to excel? in SAS Programming

How to delete empty datasets before exporting to excel?

stormblaster98 — Tue, 28 Jun 2022 02:04:15 GMT

I have this code which creates a bunch of tables and then using a macro exports them to an excel file. All the datasets are created to the WORK library and then exported. I want to know how I can edit my code to only export the datasets that have observations in them. Here is the macro I am using to export to excel:

%macro create_ut_xlsx; %macro _; %mend _;

	proc export 
	  data = WORK.UT_LIST /* UT_LIST is a table that shows all the other tables and how many observations they have */
	  dbms = xlsx
	  outfile="/home/&_CLIENTUSERID./UT_ERR_LIST.xlsx"
	  replace;
	  sheet='UT_LIST';
	run;

	%do n = 1 %to &UT_COUNT;
		%let CURR_UT_ID = %scan(%ut_list , &n, |);
		%put &CURR_UT_ID.;
	
		proc export 
		  data = WORK.&CURR_UT_ID. 
		  dbms = xlsx 
		  outfile="/home/&_CLIENTUSERID./UT_ERR_LIST.xlsx"
		  replace;
		  sheet="&CURR_UT_ID.";
		run;

		data _NULL_;
			if 0 then set WORK.&CURR_UT_ID. nobs = N;
			call symputx('CURR_UT_ID_NR_OF_ERRS', N);
			stop;
		run;
		%put In &CURR_UT_ID. the number of issues is &CURR_UT_ID_NR_OF_ERRS.;
		
		data _NULL_;
			if 0 then set WORK.&CURR_UT_ID. nobs = N;
			
		proc sql noprint;
			update UT_LIST set NR_ERR = &CURR_UT_ID_NR_OF_ERRS.
			where UT_ID = "&CURR_UT_ID.";
		quit;
	%end;

	proc export 
	  data = WORK.UT_LIST
	  dbms = xlsx
	  outfile="/home/&_CLIENTUSERID./UT_ERR_LIST.xlsx"
	  replace;
	  sheet='UT_LIST';
	run;


%mend create_ut_xlsx;

%create_ut_xlsx;

Please let me know how I can accomplish this and if you require more info.

Re: How to delete empty datasets before exporting to excel?

Tom — Tue, 28 Jun 2022 02:18:23 GMT

Your comments says you have dataset with the list of datasets and number of observations, but you don't say what variables have that information. Let's assume they are called DSNAME and NOBS.

proc export 
  data = WORK.UT_LIST
  dbms = xlsx
  outfile="/home/&_CLIENTUSERID./UT_ERR_LIST.xlsx"
  replace
;
  sheet='UT_LIST';
run;

data _null_;
  set WORK.UT_LIST ;
  where nobs > 0 ;
  call execute(catx(' '
  ,'proc export data=',dsname,'dbms = xlsx'
  ,'outfile=',quote("/home/&_CLIENTUSERID./UT_ERR_LIST.xlsx")
  ,'replace;'
  ,'sheet=',quote(trim(dsname))
  ,';run;'
  ));
run;

Notice how no macro code is needed at all.

Re: How to delete empty datasets before exporting to excel?

stormblaster98 — Tue, 28 Jun 2022 02:52:35 GMT

Sorry Im a bit new to SAS programming so Im not sure what you mean by "what variables have that information". I'll try to further explain my program and what it does hopefully that helps. Basically I have a proc sql statement which creates tables 1-78 called UT_01, UT_02, etc. These tables are created to the WORK library along with UT_LIST which has the UT_ID (Table names), NR_ERR (number of observations), UT_TEXT (Descriptor). When NR_ERR is 0 the dataset is empty, and so that UT_ID table should not be exported. Hope that helps.

Re: How to delete empty datasets before exporting to excel?

ballardw — Tue, 28 Jun 2022 03:30:09 GMT

The code as posted by @Tom does what you request. Since you did not provide any details he used NOBS instead of NR_ERR and Dsname instead of UT_ID which are variable names in your UT_LIST data set from your description.

Hint: for future questions start with a description of your data set. Better is an actual example data and best is for the example to be in the form of data step code so we have some to test.

Re: How to delete empty datasets before exporting to excel?

stormblaster98 — Tue, 28 Jun 2022 03:34:58 GMT

Yes, I was able to get it working with @Tom code so thank you for that Tom.

Thanks for the feedback will remember that for future posts.