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

[Moksha]

Thank you very much. But, I wan the following output in excel:

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

Can you please advise how to achieve this?

[Tom]

---

@Moksha wrote:

Thank you very much. But, I wan the following output in excel:

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

Can you please advise how to achieve this?

---

Move the OUTPUT statement so it only executes on the observations you want written. 

If you don't want the variable J then DROP it at some point.

Note that doing that is basic SAS data step programming.  The same types of things you should have to do in preparing any type of data for analysis in SAS.

[Moksha]

Thank you very much. It worked. Once, again thank you very much. I also each and one who has responded and gave such valuable suggestions.

[Moksha]

I have written the following code and tried to implement the suggestion given by you in this (code highlighted in BOLD BLUE color). But, in the output excel only one record is coming. I think, in this case Missingrecs dataset is getting overwritten, that's why it is displaying the only the final record.

In this code, I am getting all the files from a directory and if it not empty then writing it's contents to an excel file with separate sheet for each file. If the file is empty then I have to write the missing file name and an error message to an excel file. There are 2 empty files named dummy_Address and dummychocsales in the specified directory.

Here, is the code, please advise how to achieve to list all the missing files in an excel sheet (only in sheet).

Also, please advise if there is any better way to write the non-empty files to excel.

Code:

filename indata pipe 'dir "H:\Practice\datafiles" /b';

data file_list;

 length fname $20;

 infile indata truncover; /* infile statement for file names */

 input fname $20.; /* read the file names from the directory */

 call symput ('num_files',_n_); /* store the record number in a macro variable */

run; 

proc print data=file_list;run;

%macro fileread6;

%do j=1 %to &num_files;

%put num_files is &num_files;

data d1;

set file_list;

if _n_=&j;

call symput('filein',fname);

run;

%put filename is &filein;

%let filref=myfile;   

%let rc=%sysfunc(filename(filref,H:\Practice\datafiles\&filein));

%let fid=%sysfunc(fopen(&filref));

    %if &fid > 0 %then %do;

     %let rc=%sysfunc(fread(&fid));

     %let rc=%sysfunc(fget(&fid,mystring));       

      %if &rc = 0 %then %do; %put mystring is &mystring;

    %let str1=%sysfunc(trim(&filein));

%let str2=data;

  proc import datafile="H:\Practice\datafiles\&filein"

  out=%sysfunc(cats(%sysfunc(substr(&str1,1,3)),&str2))

  dbms=dlm

  replace;

  getnames=NO;

  run;

  proc export data=%sysfunc(cats(%sysfunc(substr(&str1,1,3)),&str2))

  outfile = "H:\Practice\outfiles\exportdata.xlsx"

  dbms=xlsx

  replace;

  sheet=%sysfunc(cats(%sysfunc(substr(&str1,1,3)),&str2));

  run;

  %end;

      %else %do; %put Warning! The file &filein is empty;

     data missingrecs;

filename&j = "&filein";

error_msg = "Warning! The file is empty";

              output;

run;

  %end;

   proc export 

  data=missingrecs

  dbms=xlsx

  outfile="H:\Practice\outfiles\Missing_records_in_source16.xlsx"

  replace

;

  sheet="MissingRecs";

run;

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

    %end;

  %let rc=%sysfunc(filename(filref));

%end; /* end of do-loop with index j */

run;

%mend fileread6;

options mprint symbolgen;

%fileread6;

OUTPUT in the excel file Missing_records_in_source16.xlsx:

filename	error_msg
dummy_Address.dat	Warning! The file is empty

Want:

filename	error_msg
dummy_Address.dat	Warning! The file is empty
dummychocsales	Warning! The file is empty

[Tom · Accepted solution]

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;

[Moksha]

Thank you very much. It worked. I have learned the better way to do this. Once again, thank very much.

[Kurt_Bremser]

Use LIBNAME XLSX and write a dataset to it.

[Moksha]

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?

[ballardw]

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.

[Moksha]

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

[Kurt_Bremser]

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.

[Moksha]

Thank you very much. But, the output of this code:

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

Can you please advise me how to get the following output:

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

[Kurt_Bremser]

Use only one PUT statement:

put i "No Records";

---

@Moksha wrote:

Thank you very much. But, the output of this code:

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

Can you please advise me how to get the following output:

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

---

[SASKiwi]

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.

[Kurt_Bremser]

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.