topic Re: Create excel file if the excel file doesn't exist and then write to it in SAS Programming

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

Moksha — Sat, 27 May 2023 16:11:20 GMT

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

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

PaigeMiller — Sat, 27 May 2023 16:32:27 GMT

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.

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

Moksha — Sat, 27 May 2023 16:40:47 GMT

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?

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

PaigeMiller — Sat, 27 May 2023 16:44:11 GMT

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

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

Kurt_Bremser — Sat, 27 May 2023 16:45:24 GMT

Use LIBNAME XLSX and write a dataset to it.

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

Moksha — Sat, 27 May 2023 17:59:58 GMT

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...

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

Moksha — Sat, 27 May 2023 19:15:44 GMT

I have tried as suggested by you, but it's not working, since I wan to use a macro and inside macro when we create dataset, cards / datalines will not work, hence used symput and symget, but not getting it. Probably, I am not doing it correctly. Can you please provide some sample code?

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

Moksha — Sat, 27 May 2023 19:18:32 GMT

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;

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

ballardw — Sat, 27 May 2023 21:12:55 GMT

Fwrite is intended to write text to a text file. IF you know the binary equivalent for other file formats you might be able to create something that file's normal program would recognize.

From the documentation for FWRITE: (emphasis mine)

FWRITE moves text from the File Data Buffer (FDB) to the external file.

Cells in spreadsheets contain a bunch of stuff other than text. A simple FWRITE, or PUT from a data step does not generate contents in the format that the programs that typically open spreadsheets to use the information as you expect. FPUT is similar.

You might be able to use a data step FILE PRINT ODS inside a ODS Excel

Suggestion: Provide a small example of the data you have as data step with the values AND an example of the spreadsheet you intend to create. Since the purpose of this spreadsheet is to demonstrate output it should only have the contents based on the small example data. It is very likely that you do not need a macro at all. Or if needed it would only be to automate such things as output file names.

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

Patrick — Sun, 28 May 2023 00:22:04 GMT

@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.

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

Tom — Sun, 28 May 2023 02:15:49 GMT

@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.

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

SASKiwi — Sun, 28 May 2023 02:43:08 GMT

I suggest you start by using the FEXIST function to test if your Excel file exists or not. I've used this approach myself and it works well. Have a look at the code example in the link and try that.

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

Tom — Sun, 28 May 2023 03:00:32 GMT

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.

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

Kurt_Bremser — Sun, 28 May 2023 10:53:17 GMT

Describe your "have" situation, with what you start (provide usable example data as needed), and describe the "want" where you want to arrive at the end. Right now, we have no real clue what you want to do.

LIBNAME XLSX does not care if an Excel file exists or not. If it exists, the file is opened and accessible as a library, if not, the library is defined, and as soon as data is written to it, the Excel file is created.

Excel files are ZIP archives of XML data; only writing some bytes to a file does not create an Excel file, but just a file with an Excel name, but unreadable garbage in it.

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

Moksha — Sun, 28 May 2023 07:44:04 GMT

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.

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

Moksha — Sun, 28 May 2023 07:45:04 GMT

Thank you very much , the link is very useful.

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

Moksha — Sun, 28 May 2023 07:50:53 GMT

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

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

PaigeMiller — Sun, 28 May 2023 09:25:06 GMT

@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.

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

Moksha — Sun, 28 May 2023 09:28:08 GMT

I have tried to use the data step using File Print as shown in the code: The output is as shown below. The problem is that the "No records" message in coming in Column A, but want that in Column B and there is one row gap between each i and message value output. But, I want that there should be no gap.

Code:

%macro odsfile3;

ods listing close;

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

options(sheet_interval='none'

sheet_name='missingrecs');

%let i = 1;

%do j=1 %to 10;

data _null_;

file print;

put "&i";

put "No records";

stop;

run;

%let i= %eval(&i+1);

%end;

ods excel close;

ods listing;

%mend odsfile3;

%odsfile3;

The output in the excel file is:

1
No records

2
No records

3
No records

4
No records

5
No records

6
No records

7
No records

8
No records

9
No records

10
No records

But, want the output as follows:

1	No Records
2	No Records
3	No Records
4	No Records
5	No Records
6	No Records
7	No Records
8	No Records
9	No Records
10	No Records

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

Kurt_Bremser — Sun, 28 May 2023 10:59:27 GMT

This is because you (ab)use the macro to perform multiple DATA steps, instead of using simple DATA step code.

ods excel
  file="C:\Practice\Missing_records_in_source5.xlsx"
  options(sheet_interval='none' sheet_name='missingrecs')
;
 
data _null_;
file print;
do i = 1 to 10;
  put i;
  put "No records";
end;
run;

ods excel close;

As often, no macro needed.