Macros not working properly

[Shakti_Sourav]

Dear Team,

Please find the below code which contains the macros in into clause, but it's not working properly, please suggest how to do this.

When I executed this below code, I got an error in &msg_w. . Also, find the screenshot for your reference.

I have tried ( " " and ' ' ) quoted mark, still not working properly, but sometimes it's working with quotation mark or without quotation mark.

Note: I have used this code in SAS Data Integration - User written Transformation and the Code Section.

Error Screenshot :

SAS CODE:

DATA work.logs;
infile  "/sasconf/Config/Lev1/SASApp/BatchServer/Logs/DLY_CORE_SAMPLING_RPT_SAMPLING_PENDENCY_REPORT_2023.12.29_09.00.43.log" truncover;
input a_line $200.;
if index(compress(a_line), 'ERROR:') > 0 or
index(compress(a_line), 'ERROR ');
run;

proc sort data=work.logs out=work.demo nodup;
by a_line ;
run;

data log1;
set work.logs;
by a_line notsorted;
flag=prxmatch("/^ERROR:/",a_line);
if flag=0 then delete;
run;

data work.W2JOY0AE0;
set log1 (obs=1);
new_string=compbl(translate(a_line,'',':()=+'));
keep new_string;
run;

proc sql noprint;
select new_string into:msg_w
from work.W2JOY0AE0;
quit;

proc sql;
update CORE.control_test
set
ERROR_MSG=&msg_w.
where TABLE_NAME='CORE_M_MIS_MDPA_TARGET';
QUIT;

[Patrick]

If your source table work.W2JOY0AE0 doesn't contain any row then the SQL into :... won't create the macro variable. To avoid this issue add before the Proc SQL.

%let msg_w=;

You also need to pass the macro variable within quotes.

ERROR_MSG="&msg_w."

Also plan what should happen if there are multiple ERROR messages in your SAS log. You ideally capture the first one. Right now your code would populate &msg_w with the last one.

[Shakti_Sourav]

Thanks for the quick reply.

I have mentioned the (obs=1) in the data step as,

data work.W2JOY0AE0;
set log1 (obs=1);
new_string=compbl(translate(a_line,'',':()=+'));
keep new_string;
run;

proc sql noprint;
select new_string into:msg_w
from work.W2JOY0AE0;
quit;

So in the above macros, msg_w contains an ERROR the work.xyz doesnot exist in a single row. 

I tried the double and single quotation mark, sometime it's working fine but not frequently.

Thanks 

Shakti

[Kurt_Bremser]

Post the complete (all code and all messages) log from the whole code you posted in the correct way I already showed you in your other thread. Do not post pictures!

[Patrick]

ok - obs=1 ensures that you only keep the first line with keyword ERROR. An alternative would be to already implement such logic in your previous data step - something like below.

data log1;
  set work.logs;
  by a_line notsorted;
  if prxmatch("/^ERROR:/",a_line) then 
    do;
      output;
      stop;
    end;
run;

You still need to define macro variable &msg_w. before the Proc SQL to ensure it does exist.

This macro variable contains a string so you certainly need to have it in double quote for passing the resolved macro variable (the string) to a SAS variable.

Furthermore:

The update will of course also execute if there is no error in your log. In this case it's going to update with a blank (IF the macro variable exists!). 

...and for things to work you need to pre-populate table CORE.control_test so that there is a row with TABLE_NAME='CORE_M_MIS_MDPA_TARGET'.  You could also consider to use the flow basename (without the datetime portion) as your key. Or eventually maintain a log table where you actually also add the datetime portion and INSERT if running for a new date.

[SASKiwi]

@Shakti_Sourav - This appears to be one of a number of posts from you recently regarding analysing SAS logs. Are you aware that SAS already provides such functionality - PROC SCAPROC? You may wish to explore use of SCAPROC, rather than coding a fully DIY solution.

[Tom]

First the logic to detect SAS error messages is wrong.  You are just looking for the string ERROR: anywhere in the line.  That will generate false positives for any place where the SAS log just happens to have those characters.  For example if you had this line in your code:

if x <= 0 then put 'ERROR: X must be positive. ' x= ;

Your search will tag it as an error even if no error message was ever written to the line.

Always looks for the ERROR text starting in column one. Also some SAS error messages have other text between the word ERROR and the colon.

Here is and example data step to collect the lines of error messages from a SAS log.

Why not add the table name into this dataset also?  And some indication of where in the file the error line appeared?  You can also add some simple logic to insure that you always get at least one observation.

data error_lines ;
  length lineno 8 table_name $32 error_msg $200 ;
  TABLE_NAME="CORE_M_MIS_MDPA_TARGET";
  if _n_=1 and eof then output;
  infile  "/sasconf/Config/Lev1/SASApp/BatchServer/Logs/DLY_CORE_SAMPLING_RPT_SAMPLING_PENDENCY_REPORT_2023.12.29_09.00.43.log"
   end=eof ;
  lineno + 1;
  input ;
  ERROR_MSG = _infile_;
  if ERROR_MSG=:'ERROR' and index(ERROR_MSG,':') then output;
run;

Not sure why you want to modify data that is already in a dataset using SQL's UPDATE statement.  But there is no need to first move the value into a macro variable.  Just update directly form the actual variable.

proc sql;
update CORE.control_test 
 set ERROR_MSG=
   (select error_msg from error_lines(obs=1) 
    where TABLE_NAME='CORE_M_MIS_MDPA_TARGET')
 where TABLE_NAME='CORE_M_MIS_MDPA_TARGET'
;
QUIT;

But is is much easier (and SAFER) to not try and change data that is already in a dataset. 

If you did need to make some type of summary dataset that combined the data from scanning multiple log files then you could just use PROC APPEND.

proc append base=summary data=error_lines(obs=1) force;
run;

Of course if you want to scan a lot of files then you could also just do that in the initial data step.  You can use the FILENAME= option on the INFILE statement to allow you to find the name of the file with the error message.  If you wanted to summarize to one line per log file you could use PROC SQL aggregate functions or even better use PROC SUMMARY.

[Shakti_Sourav]

Thanks, Tom

I have tried the same code in the SAS DI table Loader Precode & Postcode and SAS EG.

This code is working fine in SAS EG and is updated in DB and I used the same code in the SAS DI (Table Loader Precode and postcode).

Then after I execute the particular job in SAS DI, The Table loader precode and postcode not working but if I execute the table loader only, It's updating successfully.

I have attached some screenshots for your reference and also attached the code. please look into it.

My Job:

Table Loader :

ERROR_MSG=NUll in DB:

Code:

proc sql ;
select hour(JOB_START_TIME) INTO:hour
from report.CONTROL_TABLE_RPT_TEST WHERE TABLE_NAME="RPT_DISP_SHORTFALL";
quit;

%let hourr=&hour.;

%let date=%sysfunc(date(), yymmddp10.);

data error_lin;
  length lineno 8 table_name $32 error_msg $200;
  TABLE_NAME="RPT_DISP_SHORTFALL1";
  if _n_=1 and eof then output;
  infile  "/sasconf/Config/Lev1/SASApp/BatchServer/Logs/DLY_RPT_CUSTOMI_REPORT_*_RPT_DISPATCH_SHORTFALL1_&date._&hourr..*.log"
   end=eof;
  lineno + 1;
  input;
  ERROR_MSG = _infile_;
  if (ERROR_MSG=:'ERROR' and index(ERROR_MSG,':')) OR ERROR_MSG='' then output;
run;

proc sql;
update REPORT.CONTROL_TABLE_RPT_TEST 
 set ERROR_MSG=(select ERROR_MSG from error_lin(obs=1) where error_msg<>'')
 where TABLE_NAME='RPT_DISP_SHORTFALL';
QUIT;

Note: In code ( * ) means, It's indicating the whatever name or seconds are there it will take from the path by the max date which is defined in date macros.

in the server path: DLY_RPT_CUSTOMI_REPORT_5_RPT_DISPATCH_SHORTFALL1_2024.01.10_12.01.30.03937.log

trying by sas code: DLY_RPT_CUSTOMI_REPORT_*_RPT_DISPATCH_SHORTFALL1_&date._&hourr..*.log. 

It's working fine but not execute in Table Loader pre & Post code

Thanks & Regards

Shakti Sourav Mohapatra

[Patrick]

One way to debug DIS code is to just copy/paste the generated code into EG and then run it step by step. 

You don't show us all the pre- and post-code but from what you show us there is at least a quit; missing in the post-code and it is likely not identical to your current EG code version.

[Shakti_Sourav]

Thanks for the quick reply.

I have tried after the ( quit; ), but Still, It's not working.

[Patrick]

Did you copy/paste the DIS code into EG and test it from there? Does the issue this way still exist? And if yes: What's different as compared to the EG code where you state things are working?

[Shakti_Sourav]

yes, It's Updated in SAS EG. 

[Patrick]

And this is the exact code copied from DIS? Not only the pre-code but the whole program code copied into EG and run.

[Shakti_Sourav]

I have copied the whole job and executed it.

The Error_Msg variable updated successfully in DB without any error

[Tom]

You are using the YYMMDDP format to generate the periods in the day of the year part of the filename.  How do you expect to generate the periods in the time of day part of the filename?

I an see possible issues with your LOGIC.

1) The value of JOB_START_TIME in the dataset REPORT.CONTROL_TABLE_RPT_TEST does not look like the string that you need.  What type of variable is that? If numeric does it have a format attached? What format?  If it is a numeric variable with TIME values then you will want to format it properly.

2) The value from the dataset even if styled in the way you need it does not actually match the value used to name the file.  Either it is just different (perhaps by just a minute or two) or there are multiple observations selected by your first query to find the HOUR and you using the wrong one.

3) Why are you using the control dataset to find the time of day but using the system clock to find the day of the year?  What if this log was generated at 10PM and you can this program at 1AM the next day?