How to export to CSV and Excel at the same time?

[current_thing]

My code:

data _null_;

length filename $200;
set temp;
by Col1 Col2;
filename=cats('/mypath',Col1,Col2,'.txt');
file txt filevar=filename dsd delimiter="|";
if first.Col2 then
put 'Col1|Col2|Col3';
put col1 col2 col3;
run;

the above works as expected to produce the txt file. I like to incorporate export to Excel file as well. Please let me know how this can be done? Thank you!

[Kurt_Bremser]

Excel (xlsx) files are ZIP-compressed archives of XML data. Writing such in a data step would be extremely complicated, to say the least.

You have three options, all involve a separate step:

1. use PROC EXPORT
2. use LIBNAME XLSX to create the Excel file, and write your dataset to the library
3. use ODS EXCEL and an output procedure (PROC PRINT or PROC REPORT) to write the sheet.

[current_thing]

Thank you Tom. So I have the following which generates several errors in log:

data _null_ temp2;
	set my.temp1;
	by col1 col2;
	ExportLoc=cat('my dir',col1,col2,'.xlsx');
	if first.col2 then output temp2;
        if first.col2 then %test(ExportLoc);
	run;
%macro test(var1);
	proc export data=temp2 dbms=xlsx outfile=cat('"',&var1,'"') replace;
	where ExportLoc=&var1;
%mend;

Errors generated:

ERROR: Variable ExportLoc is not on file MY.TEMP1

What I thought I would be able to do is:

1) create column with the path to location with the value of Col1 and Col2

2) run macro based on subsetting of first.col2  to export based on where criteria

3) have the macro return control to the data step to continue processing

Any insight would be appreciated

[Tom]

Turn on the MPRINT option to see why your code is incorrect.

Let's expand the macro call manually so you can see that is wrong.

So you tried to use the macro call to create this SAS program.

data _null_;
....
  if first.col2 then 
	proc export data=temp2 dbms=xlsx outfile=cat('"',ExportLoc,'"') replace;
	where ExportLoc=ExportLoc;
run;

That has a series of coding mistakes.

The primary mistake is you cannot execute a PROC in the middle of data step.  Actually in this case SAS will not even recognize that as a PROC statement but just a messed up IF/THEN statement.

Second you cannot use a function call in the middle of a PROC statement like that. But there is no reason to use the CAT() function in macro code.  If you want to add quotes around the value of macro variable just expand the macro variable inside the quotes.

outfile="&var1"

Third if you want to compare the value of a variable to a string you have to use quotes around the string, just like your tried for the OUTFILE= option.

where ExportLoc="&var1";

Finally you need to define the macro BEFORE you attempt to call it.

To really make a macro like that it should have THREE parameters. What data to write.  The name of the XLSX file to write to.  The name to use for the SHEET in the XLSX file.   You can make the macro simpler if you just force the user that is calling it to add in any quotes that might be needed.

Then to run the macro once for each distinct value you could use CALL EXECUTE to push the statements onto the stack to run after the current data step finishes.

So perhaps something more like this:

%macro export(data,filename,sheet);
proc export data=&data dbms=xlsx outfile=&filename replace;
  sheet &sheet ;
run;
%mend export;

data _null_;
  set my.temp1;
  by col1 col2;
  if first.col2;
  Sheetname=catx('_',col1,col2);
  ExportLoc=cat('my dir',sheetname,'.xlsx');
  where=catx(' and ',catx('=','col1',quote(trim(col1))),catx('=','col2',quote(trim(col2))));
  call execute(cats('%nrstr(%export)
                   ,'(data=temp1(where=(',where,'))'
                   ,',filename=',quote(trim(exportloc))
                   ,',sheet=',quote(trim(sheetname))
  ));
run;

[current_thing]

Tom, Happy Sunday! I was able to solve this as follows 🙂:

data temp3;
	length exportloc $300;
	length bla $1000;
	set my.temp1;
	by col1 col2;
	ExportLoc=trim(cat("&path",col1,col2,'.xlsx'));
	bla=cat('proc export data=temp3(where=(exportloc=','"',trim(exportloc),'"',')) dbms=xlsx outfile=','"',trim(exportloc),'"',' replace; run;');
	if first.col2 then;
	call execute(bla);
run;

Thank you for the help

[ballardw]

Not actually "export" but creates files "at the same time".

There may be a way to write to Excel with a data step where you alternate the files or some such but I'm way to lazy to try such. Most of the stuff involving data step and Excel relies on DDE and Microsoft is no longer supporting that plus there are other programs that interfere with the communications method DDE uses.

Ods excel file="your path and xlsx file name goes here";

ods csv file="your path and csv file name goes here";

proc print data=temp noobs;
run;

ods csv close;
ods excel close;