Create excel file if the excel file doesn't exist and then write to it

[Moksha]

I have tried using as suggested by you using LIBNAME XLSX and dataset. Here, is the code:

Code:

libname xlout xlsx "C:\Practice\Missing_records_in_source9.xlsx";

%macro libmacro2;

   %let file1="test1.dat";

   %do j =  1 %to 10;

       %if &j=2 or &j=4 or &j=6 %then %do;

data xlout.MissingRecs;

/* input filename $ errormsg $20.;*/

/* datalines;*/

/* file1 The file is empty;*/

/*     call symput('message',"The file is empty");*/

/* error_msg=symget(&message);*/

filename = cats(&file1,&j);

error_msg = "No records";

run;

    %end;

%end;

%mend libmacro2;

%libmacro2

OUTPUT in Excel:

filename	error_msg
test1.dat6	No records

But, want the following output:

filename	error_msg
test1.dat2	No records
test1.dat4	No records
test1.dat6	No records

[Patrick]

---

@Moksha wrote:

Just now, realized that the code I have mentioned is creating an excel file if doesn't exist already. Now, the problem is that I don't know what is happening and why I am getting error (shown in the screenshot) while opening the excel file after running the macro. Can you please help me how to write to the excel file successfully?

---

The f-functions like fopen() or fwrite() are for interaction with simple text files. Your code does not create an Excel file but a Text file with name Missing_records_in_source.xlsx

Only because you give this file a suffix of xlsx doesn't make it an Excel Workbook.

[Moksha]

Thank you very much for providing insights on fopen() and fwrite(). I was thinking that it is creating excel file.