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?
@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.
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.
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 | 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 |
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;
Thank you very much. It worked. I have learned the better way to do this. Once again, thank very much.
Use LIBNAME XLSX and write a dataset to it.
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?
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.
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.
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 |
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.
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 |
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
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.
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.
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!
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.