Append string to text file in SAS

[Pradeepbanu]

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

[AndrewHowell]

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;

[andreas_lds]

What do you want to achieve? Proc printo can redirect the log to a file; %put writes text to the log.

[Pradeepbanu]

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));

[RW9]

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.

[Pradeepbanu]

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

[Kurt_Bremser]

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)

[Kurt_Bremser]

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)

[Pradeepbanu]

I added &syserr after quit it gave me proper error code but still &syserrortext doesn't work

Thanks

[ballardw]

---

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

[ballardw]

---

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