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

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

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

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

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

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:
filenameerror_msg
dummy_Address.datWarning! The file is empty

 

Want:

filenameerror_msg
dummy_Address.datWarning! The file is empty
dummychocsalesWarning! The file is empty
 
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;
Moksha
Pyrite | Level 9

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

Moksha
Pyrite | Level 9

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

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

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:

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

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

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:

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

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
PROC Star

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

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.

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
  • 2246 views
  • 3 likes
  • 7 in conversation