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

[Moksha]

Hi All,

        By using fileexist, we can find if a file exists or not. If it exists then we can open it using fopen and write to it using fput and fwrite.  But, s there a way to create an excel file if doesn't exist and then open it and write to it? I want to write to it by renaming the sheet. I want to open it in append mode to add the records. I want to do all this in a macro. Can anyone help?

So, far I am trying the following code, but after running this macro, when I try to open the excel file, getting an error message as shown in the screenshot:

Code:

%macro test;

%let filrf=myfile1;                                                                                                                     

%let rc=%sysfunc(filename(filrf, C:\practice\Missing_records_in_source.xlsx));                                                                                  

%let fid1=%sysfunc(fopen(&filrf, A, 0, V));                                                                                             

%let message = The file is empty;                                                                                                                                     

%let rc=%sysfunc(fput(&fid1, &message));                                                                                 

%if (%sysfunc(fwrite(&fid1,)) ne 0) %then %put %sysfunc(sysmsg());                                                                   

%let rc=%sysfunc(fclose(&fid1));

%mend test;

%test

In the log:

944 %macro test;
945 %let filrf=myfile1;
946 %let rc=%sysfunc(filename(filrf, C:\practice
946! \Missing_records_in_source.xlsx));
947 %let fid1=%sysfunc(fopen(&filrf, A, 0, V));
948 %let message = The file is empty;
949
950 %let rc=%sysfunc(fput(&fid1, &message));
951 %if (%sysfunc(fwrite(&fid1,)) ne 0) %then %put %sysfunc(sysmsg());
952
953 %let rc=%sysfunc(fclose(&fid1));
954 %mend test;
955
956 %test
SYMBOLGEN: Macro variable FILRF resolves to myfile1
SYMBOLGEN: Macro variable FID1 resolves to 5
SYMBOLGEN: Macro variable MESSAGE resolves to The file is empty
SYMBOLGEN: Macro variable FID1 resolves to 5
SYMBOLGEN: Macro variable FID1 resolves to 5

Error when tried to open the excel file:

Thanks

[PaigeMiller]

I'm a little confused by your description.

But, s there a way to create an excel file if doesn't exist and then open it and write to it?

You could create it and write to it all at the same time in one action. I'm not aware of a way to create a new blank empty Excel file in one step, and then write to it in the next step. Or maybe this is all the same thing, said with different words.

[Moksha]

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?

[PaigeMiller]

I would say you should not use fopen/fwrite to create or write to an Excel file, it wasn't meant for that.

Use ODS EXCEL or PROC EXPORT

[Moksha]

I have tried these options, but it's not working as expected. Actually, what I am trying to do is:

Loop through the files in a directory

check if each file is empty or not.

 if the file is empty then write filename and message like "The file <filename> is empty" to an excel file.

 I wan to do this in a macro. 

I am not able to do the else part.

After executing the macro, I need to have an excel file with the names of empty files and a message for each file., say sheet1's (name to be MissingRecs) A1 cell will have empty file name1 A2 cell will have error message

                                 B1 will have next empty file name2 B2 will have corresponding error message so on...

[Tom]

---

@Moksha wrote:

I have tried these options, but it's not working as expected. Actually, what I am trying to do is:

Loop through the files in a directory

check if each file is empty or not.

 if the file is empty then write filename and message like "The file <filename> is empty" to an excel file.

 I wan to do this in a macro. 

I am not able to do the else part.

After executing the macro, I need to have an excel file with the names of empty files and a message for each file., say sheet1's (name to be MissingRecs) A1 cell will have empty file name1 A2 cell will have error message

                                 B1 will have next empty file name2 B2 will have corresponding error message so on...

---

Create a SAS dataset with the information you want and then at the end write the dataset to a worksheet in the XLSX workbook.

And you definitely do not want to use %SYSFUNC() to call SAS functions in a macro just so you can get data into a dataset.  Use the SAS functions directly in the data step instead.  That is MUCH MUCH easier to debug than macro language.

[Tom]

First get the list of files in the directory.  That is a know problem with many posted solutions.  Try https://github.com/sasutils/macros/blob/master/dirtree.sas

Then check if the files are empty.  If that means they have zero bytes then the output of %DIRTREE() already tells you that. Look at the SIZE variable.

[Moksha]

Thank you very much , the link is very useful.

[PaigeMiller]

@Moksha 

I see a lot has happened ... and you appear to have marked a solution as correct. However, I wanted to comment on this:

---

@Moksha wrote:

I have tried these options, but it's not working as expected.

---

and also on this comment to Kurt Bremser

I have tried as suggested by you, but it's not working, since ...

You can't say something isn't working without providing details. That is a useless thing to do. If that's all you say, we can't help you. We don't know what you did (we don't know what code you tried) and we don't know what happened that indicates something isn't working. From now on, when something isn't working, if there are errors in the log, show us the entire log. If the log is fine but you aren't getting the right output, you need to show us your code and the output and explain what is wrong.

[Moksha]

Thanks for pointing out. I should have given the code that I have tried. Today, I have provided the code that I have tried based on the suggestion given by kurt_Bremser  and you. 

Further, I have been trying on this using different solutions and tried the following code also:

Code:

%macro odsfile2;

ods listing close;

ods excel file="C:\Practice\Missing_records_in_source10.xlsx"

    options(sheet_interval='none'

        sheet_name='missingrecs'

);

%let file1=test1.dat;

   data _null_;

declare odsout t();

t.table_start();

t.row_start();

t.format_cell(data:"File Name");

t.format_cell(data: "Error Message");

t.table_End();

t.row_end();

t.table_start();

run;

   %do j =  1 %to 10;

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

data _null_;

declare odsout t();

t.table_start();

t.row_start();  

t.format_Cell(data: "&file1&j");

t.format_cell(data: "Warning! The file ""&file1&j"" is empty");

t.row_End();

t.table_End();

run;

    %end;

t.table_end();

%end;

run;

ods excel close;

ods listing;

%mend odsfile2;

%odsfile2;

Output in excel:

File Name	Error Message
test1.dat2	Warning! The file "test1.dat2" is empty
test1.dat4	Warning! The file "test1.dat4" is empty
test1.dat6	Warning! The file "test1.dat6" is empty

But, Want the following output:

File Name	Error Message
test1.dat2	Warning! The file "test1.dat2" is empty
test1.dat4	Warning! The file "test1.dat4" is empty
test1.dat6	Warning! The file "test1.dat6" is empty

I think that due to b t.table_start() and t.table_end(), there is a row gap between each output row. But, I am not sure how to eliminate this row gap.

[Kurt_Bremser]

Stop abusing the macro language. YOU DO NOT NEED A MACRO FOR THIS.

---

@Moksha wrote:

Thanks for pointing out. I should have given the code that I have tried. Today, I have provided the code that I have tried based on the suggestion given by kurt_Bremser  and you. 

Further, I have been trying on this using different solutions and tried the following code also:

Code:

%macro odsfile2;

ods listing close;

ods excel file="C:\Practice\Missing_records_in_source10.xlsx"

    options(sheet_interval='none'

        sheet_name='missingrecs'

);

%let file1=test1.dat;

   data _null_;

declare odsout t();

t.table_start();

t.row_start();

t.format_cell(data:"File Name");

t.format_cell(data: "Error Message");

t.table_End();

t.row_end();

t.table_start();

run;

   %do j =  1 %to 10;

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

data _null_;

declare odsout t();

t.table_start();

t.row_start();  

t.format_Cell(data: "&file1&j");

t.format_cell(data: "Warning! The file ""&file1&j"" is empty");

t.row_End();

t.table_End();

run;

    %end;

t.table_end();

%end;

run;

ods excel close;

ods listing;

%mend odsfile2;

%odsfile2;

Output in excel:

File Name	Error Message
test1.dat2	Warning! The file "test1.dat2" is empty
test1.dat4	Warning! The file "test1.dat4" is empty
test1.dat6	Warning! The file "test1.dat6" is empty

But, Want the following output:

File Name	Error Message
test1.dat2	Warning! The file "test1.dat2" is empty
test1.dat4	Warning! The file "test1.dat4" is empty
test1.dat6	Warning! The file "test1.dat6" is empty

I think that due to b t.table_start() and t.table_end(), there is a row gap between each output row. But, I am not sure how to eliminate this row gap.

---

[Moksha]

I was trying it continuously and now the following code is writing to the excel file in a macro, I am not able to understand how to write the message to the cell B1. Also, there are multiple empty files, but it is writing only one file. I have put this code in a loop and reading each file. Here &filein is having the value of the file name. What I have observed is that the Cell A is getting overwritten when more than one empty file exists.

Can you please tell me how to make it not to overwrite the cell and how to write to cell A1 and B1 and next empty file in Cell A2 and corresponding warning message in Cell B2 so on...?

Code:

ods listing close;
ods excel file="C:\Practice\Missing_records_in_source2.xlsx"
options(sheet_name='missingrecs');
data _null_;
file print;
put "&filein";
put "Warning! The file &filein is empty";
stop;
run;
ods excel close;
ods listing;

[Moksha]

Thanks PaigeMiller, as I have mentioned in my previous reply, ODS Excel has worked and only thing left is that the cells are getting overwritten, I will try to solve it, probably I have to adjust the looping.  Once again, thank you very much. Your suggestion helped me move forward.

[Moksha]

I have tried using PROC EXPORT also as suggested by you. Here is the code:

Code:

%macro exportmacro2;

%let file1="test1.dat";

%do j=1 %to 10;

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

data missingrecs;

/* input filename $ message $;*/

/*    filename = symget(&file1);*/

filename = cats(&file1,&j);

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

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

error_msg = "No records";

run;

proc export data=missingrecs

     outfile="C:\Practice\Missing_records_in_source12.xlsx"

dbms=xlsx

replace;

sheet="MissingRecs";

%end;

run;

%end;

%mend exportmacro2;

options mprint symbolgen;

%exportmacro2

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

Can you please advise how to achieve this?

[Tom]

Make the data first. Then write it to EXCEL.  Do not use MACRO CODE to replace normal SAS code.  The macro processor is just to help you generate SAS code.

%macro exportmacro2;
%let file1="test1.dat";


data missingrecs;
  do j=1 to 10;
    filename = cats(&file1,j);
    if j in (2 4 6) then error_msg = "No records";
    else error_msg=" ";
    output;
  end;
run;
 
proc export 
  data=missingrecs
  dbms=xlsx
  outfile="C:\downloads\Missing_records_in_source12.xlsx"
  replace
;
  sheet="MissingRecs";
run;
 
%mend exportmacro2;
options mprint symbolgen;
%exportmacro2;

73   options mprint symbolgen;
74   %exportmacro2;
MPRINT(EXPORTMACRO2):   data missingrecs;
MPRINT(EXPORTMACRO2):   do j=1 to 10;
SYMBOLGEN:  Macro variable FILE1 resolves to "test1.dat"
MPRINT(EXPORTMACRO2):   filename = cats("test1.dat",j);
MPRINT(EXPORTMACRO2):   if j in (2 4 6) then error_msg = "No records";
MPRINT(EXPORTMACRO2):   else error_msg=" ";
MPRINT(EXPORTMACRO2):   output;
MPRINT(EXPORTMACRO2):   end;
MPRINT(EXPORTMACRO2):   run;

NOTE: The data set WORK.MISSINGRECS has 10 observations and 3 variables.
NOTE: DATA statement used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds


MPRINT(EXPORTMACRO2):   proc export data=missingrecs dbms=xlsx outfile="C:\downloads\Missing_records_in_source12.xlsx" replace ;
MPRINT(EXPORTMACRO2):   WXLX;
MPRINT(EXPORTMACRO2):   sheet="MissingRecs";
MPRINT(EXPORTMACRO2):   run;

NOTE: The export data set has 10 observations and 3 variables.
NOTE: "C:\downloads\Missing_records_in_source12.xlsx" file was successfully created.
NOTE: PROCEDURE EXPORT used (Total process time):
      real time           0.01 seconds
      cpu time            0.01 seconds