BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
Moksha
Pyrite | Level 9

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:

 

Moksha_0-1685203636436.png

 

Thanks

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Tom
Super User Tom
Super User

Why do keep making it harder by trying to do data manipulation in macro code instead of in actual code?

 

Try something like this:

%macro fileread6;
 
data file_list2;
  set file_list nobs=nobs;
  length dsname $32 message $100 ;
  if _n_=1 then put "num_files is " nobs ;
* Make FILEREF pointing to current file ;
  rc=filename('myfile',cats('H:\Practice\datafiles\',fname));
* OPEN current file ;
  fid=fopen('myfile');
  if fid<=0 then message="Unable to open file.";
  else do;
* READ first record from current file ;
    if fread(fid) then message="Unable to read from file.";
    else do;
* GET first byte from current file ;
      if fget(fid,message,1) then message="Unable to get anything from file.";
      else do;
* File was found ;
        message='File found.';
* Make a dataset name for the file ;
        dsname=cats(substr(fname,1,3),'data'));
* Generate code to IMPORT the file ;
        call execute(catx(' '
          ,'proc import datafile=',quote(cats("H:\Practice\datafiles\",fname))
          ,'out=',dsname
          ,'dbms=dlm','replace;'
          ,'getnames=NO;'
          ,'run;'
        ));
* Generate code to EXPORT the imported file ;
        call execute(catx(' '
          ,'proc export data=',dsname
          ,'outfile="H:\Practice\outfiles\exportdata.xlsx"'
          ,'dbms=xlsx','replace;'
          ,'sheet=',dsname,';'
          ,'run;'
        ));
      end;
    end;
* CLOSE the file;
    rc=fclose(fid);
  end;
* CLEAR the fileref;
  rc=filename('myfile');
  drop rc fid;
run;

* Write the messages about missing files ;
proc export 
  data=file_list2(where=(message ^= 'File found.'))
  dbms=xlsx
  outfile="H:\Practice\outfiles\Missing_records_in_source16.xlsx"
  replace
;
  sheet="MissingRecs";
run;
 
%mend fileread6;

View solution in original post

32 REPLIES 32
PaigeMiller
Diamond | Level 26

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.

--
Paige Miller
Moksha
Pyrite | Level 9

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
Diamond | Level 26

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

--
Paige Miller
Moksha
Pyrite | Level 9

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
Super User Tom
Super User

@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
Super User Tom
Super User

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
Pyrite | Level 9

Thank you very much , the link is very useful.

PaigeMiller
Diamond | Level 26

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

 

--
Paige Miller
Moksha
Pyrite | Level 9

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 NameError Message
  
test1.dat2Warning! The file "test1.dat2" is empty
  
test1.dat4Warning! The file "test1.dat4" is empty
  
test1.dat6Warning! The file "test1.dat6" is empty

 

But, Want the following output:

File NameError Message
test1.dat2Warning! The file "test1.dat2" is empty
test1.dat4Warning! The file "test1.dat4" is empty
test1.dat6Warning! 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
Super User

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
Pyrite | Level 9

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
Pyrite | Level 9

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
Pyrite | Level 9

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:
filenameerror_msg
test1.dat6No records

 

But, want the following output:

filenameerror_msg
test1.dat2No records
test1.dat4No records
test1.dat6No records

Can you please advise how to achieve this?

 

Tom
Super User Tom
Super User

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

Tom_0-1685281775725.png

 

 

SAS Innovate 2025: Call for Content

Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!

Submit your idea!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 32 replies
  • 2205 views
  • 3 likes
  • 7 in conversation