I wanna append string to a text file at beginning and end of proc sql statment, I tried like below
libname DXZ 'libpath';
%macro processlogger(msg);
filename logfile '../Processlog/processlog.txt';
data _null_;
file logfile;
put "%superq(message)";
run;
%mend;
%processlogger ('Begin');
proc sql;
select * from DZ.NoofDaysin_Reje /* Mispelled name */
run;
%processlogger('End');
I seems to messing up in macro variable, is there any other way I can do this, Thanks
First thought - be consistent in the naming of your macro variable: msg or message?
Plus you'll need to modify your filename statement - currently each data/run code will overwrite the logfile. (Use the MOD option).
Example, from http://support.sas.com/kb/24/569.html
/* Create base file */
data _null_;
infile datalines truncover;
file "C:\Temp\base_file.txt";
input fruit :$10.;
put fruit;
datalines;
apple
banana
coconut
;
run;
/* Append information to the existing file using MOD option */
data _null_;
file "c:\Temp\base_file.txt" mod;
input fruit :$10.;
put fruit;
datalines;
date
elderberry
fig
;
run;
What do you want to achieve? Proc printo can redirect the log to a file; %put writes text to the log.
I am trying to achieve to add step by step logger on my each Proc sql select statement, I was able to do that
by below code, I couldn't append syserrortext in to the file
%macro processlogger(msg);
data _null_;
file '../Processlog/processlog.txt' mod;
length message $32767 ;
message=symget('msg');
put message ;
run;
%mend;
%processlogger(Starting at %sysfunc(datetime(),datetime24.3));
Proc SQL;
select * from DZ.Table_name
run;
%put SYSERR = &SYSERR;
/* If I add this I am getting warning */
%put SYSERRORTEXT = &SYSERRORTEXT;
Apparent Symbolic reference SYSERRORTEXT not resolved /* here I have to append error text also in my file */
%processlogger(Ending at SYSERR,SYSERRORTEXT %sysfunc(datetime(),datetime24.3));
I am trying to achieve to add step by step logger on my each Proc sql select statement
Or you could use the log which is what it is for? There is no point in re-inventing the wheel. Write Base SAS code, run Base SAS code, examine the log if there are any problems.
Please explain what it is your trying to do, in full, not little bits. Show some input test data in the form of a datastep and what you want out. Just looking at your posts you seem to be wanting to create on Oracle, some list of operations from SAS, i.e. you have seen the audit trail on oracle and want to use that in conjunction with SAS programming. Not really a good idea, what is good for one system is not necessarily good for another. As SAS provides full logs for each run, that is the equivalent of the audit trail for Oracle. Store that log as part of your output package.
This is what I am trying to do:
1. Create Log file say logfile.txt
2. I run a sas code to fetch all reject details from sas or Orcale table using proc sql
3. Before proc sql begins I will write/append a plain normal string into my logfile.txt saying that "Step:1 Begin of ZZZZZZ" and same for at end of the proc sql
4. Purpose of this is track all codes log in single file, for example any of the table got deleted accidentally I will get get an "not exist error", i need to track this error in my logfile.txt, and to do so I did like
%processorlogger(Step:2 End of noofdaysin_reject at &SYSERR &SYSERRORTEXT %sysfunc(datetime(),datetime24.3));
at syserrortext I am getting a warning says "Apparent symbolic reference SYSERRORTEXT not resolved"
I am getting the proper error code 1012 for syserr and it gets appends into the file but syserrortext doesn't.., Please note if &syserror was given without quitting proc sql syserr code will be always 0 which is success, so for precise error syserror should be given after quit like below
proc sql;
select * from DZ.noofdaysin_re /* this is mispelled table */
run;
quit;
%put SYSERR = &SYSERR;
%put SYSERRORTEXT = &SYSERRORTEXT;
Hope I made my point clear..!!
There's something else at play here.
When I run this code:
%macro processlogger(msg);
data _null_;
file '$HOME/sascommunity/processlog.txt' mod;
length message $32767 ;
message="&msg";
put message ;
run;
%mend;
%processlogger(Starting at %sysfunc(datetime(),datetime24.3));
Proc SQL;
select * from sashelp.xxx;
quit;
%put SYSERR = &SYSERR;
%put SYSERRORTEXT = &SYSERRORTEXT;
%processlogger(Ending at &SYSERR &SYSERRORTEXT %sysfunc(datetime(),datetime24.3));
causing the obvious error, I get this log:
26 GOPTIONS ACCESSIBLE; 27 %macro processlogger(msg); 28 data _null_; 29 file '$HOME/sascommunity/processlog.txt' mod; 30 length message $32767 ; 31 message="&msg"; 32 put message ; 33 run; 34 %mend; 35 36 37 %processlogger(Starting at %sysfunc(datetime(),datetime24.3)); NOTE: The file '$HOME/sascommunity/processlog.txt' is: Dateiname=/wadaten/home/e9782/sascommunity/processlog.txt, Besitzername=e9782,Gruppenname=sasadmin, Zugriffsberechtigung=-rw-r--r--, Zuletzt geändert=15. März 2018 17.03 Uhr, Dateigröße (Byte)=0 NOTE: 1 record was written to the file '$HOME/sascommunity/processlog.txt'. The minimum record length was 36. The maximum record length was 36. NOTE: DATA statement used (Total process time): real time 0.01 seconds cpu time 0.00 seconds 38 39 Proc SQL; 40 select * from sashelp.xxx; ERROR: Datei SASHELP.XXX.DATA existiert nicht. 2 Das SAS System 14:53 Thursday, March 15, 2018 NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements. 41 quit; NOTE: The SAS System stopped processing this step because of errors. NOTE: PROZEDUR SQL used (Total process time): real time 0.00 seconds cpu time 0.00 seconds 42 43 %put SYSERR = &SYSERR; SYSERR = 1012 44 45 %put SYSERRORTEXT = &SYSERRORTEXT; SYSERRORTEXT = Datei SASHELP.XXX.DATA existiert nicht. 46 47 48 %processlogger(Ending at &SYSERR &SYSERRORTEXT %sysfunc(datetime(),datetime24.3)); NOTE: The file '$HOME/sascommunity/processlog.txt' is: Dateiname=/wadaten/home/e9782/sascommunity/processlog.txt, Besitzername=e9782,Gruppenname=sasadmin, Zugriffsberechtigung=-rw-r--r--, Zuletzt geändert=15. März 2018 17.03 Uhr, Dateigröße (Byte)=37 NOTE: 1 record was written to the file '$HOME/sascommunity/processlog.txt'. The minimum record length was 80. The maximum record length was 80. NOTE: DATA statement used (Total process time): real time 0.00 seconds cpu time 0.00 seconds
(German messages, but &syserrortext resolves)
and this is the resulting logfile:
Starting at 15MAR2018:17:03:25.744 Ending at 1012 Datei SASHELP.XXX.DATA existiert nicht. 15MAR2018:17:03:25.806
which also contains the correct message.
Try to run my code as is (just change the logfile name/location)
This works for me:
%macro processlogger(msg);
data _null_;
file '$HOME/sascommunity/processlog.txt' mod;
length message $32767 ;
message="&msg";
put message ;
run;
%mend;
%processlogger(Starting at %sysfunc(datetime(),datetime24.3));
Proc SQL;
select * from sashelp.class;
run;
%put SYSERR = &SYSERR;
%put SYSERRORTEXT = &SYSERRORTEXT;
%processlogger(Ending at &SYSERR &SYSERRORTEXT %sysfunc(datetime(),datetime24.3));
Note that I omitted the comma in the second macro call, as it causes the macro processor to see two parameters instead of one.
You might also consider to use &syscc instead of &syserr, as it seems &syserr is not set by proc sql. (I used an invalid library in the SQL and still got zero)
I added &syserr after quit it gave me proper error code but still &syserrortext doesn't work
Thanks
@Pradeepbanu wrote:
I added &syserr after quit it gave me proper error code but still &syserrortext doesn't work
Thanks
It may help to show the code and messages from the log including the code that is supposed to throw the error.
So far you have only shown the code that you may have submitted without the log output. It wouldn't hurt to add
options mprint symbolgen; before the macro call as well.
@Pradeepbanu wrote:
I am trying to achieve to add step by step logger on my each Proc sql select statement, I was able to do that
by below code, I couldn't append syserrortext in to the file
%macro processlogger(msg); data _null_; file '../Processlog/processlog.txt' mod; length message $32767 ; message=symget('msg'); put message ; run; %mend;
%processlogger(Starting at %sysfunc(datetime(),datetime24.3));
Proc SQL;
select * from DZ.Table_name
run;
%put SYSERR = &SYSERR;
/* If I add this I am getting warning */
%put SYSERRORTEXT = &SYSERRORTEXT;
Apparent Symbolic reference SYSERRORTEXT not resolved /* here I have to append error text also in my file */
%processlogger(Ending at SYSERR,SYSERRORTEXT %sysfunc(datetime(),datetime24.3));
Your specific call to the macro
%processlogger(Ending at SYSERR,SYSERRORTEXT %sysfunc(datetime(),datetime24.3));
would fail as you have only defined one parameter and the comma between Syserr and Syserror text would tell the processor that you have 2 parameters. See this very simple example:
%macro dummy (msg); %put Message is &message; %mend; %dummy(Simple message); %dummy( Message, with a comma);
The last line generates an error an the macro does not execute.
Potentially you have the same issue with &syserrortext possibly containing one or more commas in the resulting text also causing an issue. So you may have some fun with macro quoting.
Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.
Ready to level-up your skills? Choose your own adventure.