topic Re: Error in code to be executed repeatedly in SAS Data Management

Error in code to be executed repeatedly

mchiappe83 — Wed, 13 Jun 2018 23:35:01 GMT

Hi Everyone,

I have a code that, if ran manually (n times in a row) in SAS Guide performs perfectly well. Now, if I try to execute this code in DIS, it comes up with errors from the second time it is run. Any idea of what may be happening?

Many thanks,

Martín

Re: Error in code to be executed repeatedly

LaurieF — Thu, 14 Jun 2018 01:11:44 GMT

(Edited) log please?

Re: Error in code to be executed repeatedly

mchiappe83 — Fri, 15 Jun 2018 18:33:10 GMT

Thanks a lot for your response. Please see the both the code and log attached. Let me know your thoughts.

Re: Error in code to be executed repeatedly

LaurieF — Thu, 14 Jun 2018 01:43:17 GMT

It's too much to go through. Can you edit it down to just the bits around the error? say the data/proc steps either side?

Re: Error in code to be executed repeatedly

mchiappe83 — Thu, 14 Jun 2018 02:12:36 GMT

No problem. The first error occurs in the following step:

958 proc sql ;
959 insert into xxxx.STG_SPESERV_PADRON_AUTO
960 select * from z.XDAUTO
961 WHERE PUT(F_LOG, 8.)||put(H_LOG,Z6.) > put(&fecha,Z14.);
ERROR: The file Z.XDAUTO.DATA does not exist.
NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.
962 quit;
NOTE: The SAS System stopped processing this step because of errors.

The file XDAUTO is being deleted in the very beggining of the code, in order to ensure there's nothing called this way when creating this file inmediately after. Then, the error stated above occurs. At the end of the code, the file is being deleted again in orden to leave the directory clean for the next execution of the code.

Let me know If you followed me.

Many thanks

Re: Error in code to be executed repeatedly

Tom — Thu, 14 Jun 2018 03:34:43 GMT

I don't understand. You deleted the file and then tried to write to it and are surprised it caused an error?

I don't see anything in the log where it worked once or where the file is created.

If you deleted it then you need to create it again before you can use INSERT to put records into it.

Re: Error in code to be executed repeatedly

Kurt_Bremser — Thu, 14 Jun 2018 06:32:31 GMT

The crucial error happens earlier:

228        LIBNAME Z BASE "/u/SAS/datos";
NOTE: La librería Z no existe.

Previously, you used a remote connection for library Z:

184        libname z "/u/SAS/datos" SERVER=ts12;
NOTE: Libref Z was successfully assigned as follows: 
      Engine:        REMOTE 
      Physical Name: /u/SAS/datos

Re: Error in code to be executed repeatedly

mchiappe83 — Thu, 14 Jun 2018 12:48:13 GMT

Thanks @Kurt_Bremser

Let me understand your point and then I share my thought.

You say the crucial error comes with the following sentence in the code:

libname z "/u/SAS/datos" SERVER=ts12;

And that, previously, I'm using a remote connection.

In the code this sentence is being used only once and:

1) the very first thing the log informs is that Libref Z was successfully created

2) then, in line 228, the log informs that library Z does not exist -what I don't understand just because of 1)-

3) when ran manually this code in SAS Guide, over and over again, no error shows up.

Please let us know your thoughts. I appreciate your help very much.

Martin

Re: Error in code to be executed repeatedly

mchiappe83 — Thu, 14 Jun 2018 12:57:37 GMT

Thanks @Tom for your response.

The file that is being reported in the error is being created in a previous step, inside LIBNAME DATOS '/u/SAS/datos';

Then LIBNAME DATOS '/u/SAS/datos' is being renamed with the sentence libname z "/u/SAS/datos" SERVER=ts12 so I expect that the file reported in the error is contained in this new libname.

Are my thoughts correct? Many thanks,

Martin

Re: Error in code to be executed repeatedly

Tom — Thu, 14 Jun 2018 13:05:15 GMT

@mchiappe83 wrote:

Thanks @Tom for your response.

The file that is being reported in the error is being created in a previous step, inside LIBNAME DATOS '/u/SAS/datos';

Then LIBNAME DATOS '/u/SAS/datos' is being renamed with the sentence libname z "/u/SAS/datos" SERVER=ts12 so I expect that the file reported in the error is contained in this new libname.

Are my thoughts correct? Many thanks,

Martin

Z is a different libref than DATOS, even if they are both ultimately pointing to the same physical location.

Make sure the code is using the libref that is active.

Re: Error in code to be executed repeatedly

Kurt_Bremser — Thu, 14 Jun 2018 13:16:44 GMT

In lines 176 to 178 you sign on to a r4emote SAS/CONNECT server, which is running on z/OS:

176        %let ts12=host.test.arba.gov.ar 7541;
177        options comamid=tcp remote=ts12;
178        signon user="consusas" password=XXXXXXXXX;

From log line 182 you remote submit code to that server, which returns 45 lines of its log before the rsubmit ends.

In log line 184 you assign a library Z via RLS to that CONNECT server:

184        libname z "/u/SAS/datos" SERVER=ts12;
NOTE: Libref Z was successfully assigned as follows: 
      Engine:        REMOTE 
      Physical Name: /u/SAS/datos

which obviously succeeds.

In log line 228 you have another libname statement, which tries to assign library Z with a local path (no server specified as in line 184):

228        LIBNAME Z BASE "/u/SAS/datos";
NOTE: La librería Z no existe.

which fails, as can be seen by the NOTE. This leaves you with an unassigned library Z, so all future references to datasets in library Z will fail.

This non-existence of library Z causes the ERRORs happening from log line 958:

958        proc sql ;
959         insert into xxxx.STG_SPESERV_PADRON_AUTO
960        	select * from  z.XDAUTO
961        	 WHERE PUT(F_LOG, 8.)||put(H_LOG,Z6.) > put(&fecha,Z14.);
ERROR: El archivo Z.XDAUTO.DATA no existe.
NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.
962        quit;
NOTE: The SAS System stopped processing this step because of errors.
NOTE: SAS set option OBS=0 and will continue to check statements. This might cause NOTE: No observations in data set.

You now have no dataset xxxx.STG_SPESERV_PADRON_AUTO, and since OBS=0, and SAS has gone into syntax check mode, you have NOEXEC in the SQL following later:

1240       proc sql;
NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.
1241       		
1242       	select max(f_log) INTO :HOY from xxxx.STG_SPESERV_PADRON_AUTO;
NOTE: Statement not executed due to NOEXEC option.
1243       	select max(h_log) into :hora from xxxx.STG_SPESERV_PADRON_AUTO where f_log = (select max(f_log) from
1243     ! xxxx.STG_SPESERV_PADRON_AUTO );
NOTE: Statement not executed due to NOEXEC option.
1244       
1245       	
1246       	UPDATE xxxx.parametro_procesamiento AS a
1247       	SET  fecha_ult_ejec = put(&hoy, 8.) || put(&hora, z6.),
                                      _
                                      22
WARNING: Apparent symbolic reference HOY not resolved.

which also causes that no macro variables are created. From then on, all hell breaks loose, for obvious reasons.

Re: Error in code to be executed repeatedly

Kurt_Bremser — Thu, 14 Jun 2018 13:27:07 GMT

@mchiappe83 wrote:

Thanks @Tom for your response.

The file that is being reported in the error is being created in a previous step, inside LIBNAME DATOS '/u/SAS/datos';

Then LIBNAME DATOS '/u/SAS/datos' is being renamed with the sentence libname z "/u/SAS/datos" SERVER=ts12 so I expect that the file reported in the error is contained in this new libname.

Are my thoughts correct? Many thanks,

Martin

You have here a fundamental misunderstanding of what happens where.

LIBNAME DATOS '/u/SAS/datos'; is being executed during an rsubmit to the remote server, so a local assignment using the BASE engine works.

libname z "/u/SAS/datos" SERVER=ts12; is executed in the local context, but uses the REMOTE engine to the server, so it works also.

But LIBNAME Z BASE "/u/SAS/datos"; is used once again in the local context (not during an rsubmit!) without specifying the server, and so it fails, as that path is not available in your local SAS session. That failed libname effectively clears the libref for Z. Either remove that statement (and keep on using the previously successfully assigned remote library), or specify the server.

Re: Error in code to be executed repeatedly

mchiappe83 — Thu, 14 Jun 2018 19:37:33 GMT

Hi @Kurt_Bremser

Thanks a lot for your guidance

But LIBNAME Z BASE "/u/SAS/datos"; is used once again in the local context (not during an rsubmit!) without specifying the server, and so it fails, as that path is not available in your local SAS session. That failed libname effectively clears the libref for Z. Either remove that statement (and keep on using the previously successfully assigned remote library), or specify the server.

I'm not being able to understand where does LIBNAME Z BASE "/u/SAS/datos" happens, as there's no place in the code where I write it down strictly that way.

From the log, it seems to have place before the following piece of code:

proc sql;
 insert into xxxx.STG_SPESERV_PADRON_AUTO
	select * from  z.XDAUTO 
	 WHERE PUT(F_LOG, 8.)||put(H_LOG,Z6.) > put(&fecha,Z14.);

But there's nothing like LIBNAME Z BASE "/u/SAS/datos" there.

Thanks,

Martin

Thanks again for your time.

Martin

Re: Error in code to be executed repeatedly

Kurt_Bremser — Thu, 14 Jun 2018 18:19:53 GMT

@mchiappe83 wrote:

Hi @Kurt_Bremser

Thanks a lot for your guidance

But LIBNAME Z BASE "/u/SAS/datos"; is used once again in the local context (not during an rsubmit!) without specifying the server, and so it fails, as that path is not available in your local SAS session. That failed libname effectively clears the libref for Z. Either remove that statement (and keep on using the previously successfully assigned remote library), or specify the server.

I'm not being able to understand where does LIBNAME Z BASE "/u/SAS/datos" happens, as there's no place in the code where I write it down strictly that way.

Thanks again for your time.

Martin

Look at the log. I gave you all log line numbers.

Re: Error in code to be executed repeatedly

mchiappe83 — Thu, 14 Jun 2018 19:47:41 GMT

In log line 228 you have another libname statement, which tries to assign library Z with a local path (no server specified as in line 184):
228        LIBNAME Z BASE "/u/SAS/datos";
NOTE: La librería Z no existe.
which fails, as can be seen by the NOTE. This leaves you with an unassigned library Z, so all future references to datasets in library Z will fail.

I'm not being able to understand why this piece of log shows up. I believe that's because I'm calling z.xdauto in the following step

proc sql;
 insert into xxxx.STG_SPESERV_PADRON_AUTO
	select * from  z.XDAUTO 
	 WHERE PUT(F_LOG, 8.)||put(H_LOG,Z6.) > put(&fecha,Z14.);

Why the following statement (written previously in the code) isn't useful to this end?

libname z "/u/SAS/datos" SERVER=ts12;

Thanks,

Martin

Re: Error in code to be executed repeatedly

mchiappe83 — Thu, 14 Jun 2018 19:51:50 GMT

Z is a different libref than DATOS, even if they are both ultimately pointing to the same physical location.
Make sure the code is using the libref that is active

How can I make sure of this?

Thanks,

Martin

Re: Error in code to be executed repeatedly

Kurt_Bremser — Thu, 14 Jun 2018 20:28:33 GMT

@mchiappe83 wrote:
In log line 228 you have another libname statement, which tries to assign library Z with a local path (no server specified as in line 184):
228        LIBNAME Z BASE "/u/SAS/datos";
NOTE: La librería Z no existe.
which fails, as can be seen by the NOTE. This leaves you with an unassigned library Z, so all future references to datasets in library Z will fail.
I'm not being able to understand why this piece of log shows up. I believe that's because I'm calling z.xdauto in the following step
proc sql;
 insert into xxxx.STG_SPESERV_PADRON_AUTO
	select * from  z.XDAUTO 
	 WHERE PUT(F_LOG, 8.)||put(H_LOG,Z6.) > put(&fecha,Z14.);
Why the following statement (written previously in the code) isn't useful to this end?
libname z "/u/SAS/datos" SERVER=ts12;
Thanks,

Martin

Because (as I already explained) the following wrong libname statement clears the libref.

And code appears in the log because you ran it, period.

Pay attention to details and make sure that code and logs are from the same execution.

Re: Error in code to be executed repeatedly

mchiappe83 — Thu, 14 Jun 2018 20:37:42 GMT

Thanks @Kurt_Bremser

Look at the log. I gave you all log line numbers.

I did, but I can't figure out why LIBNAME Z BASE "/u/SAS/datos"; is automatically being generated if libname z "/u/SAS/datos" SERVER=ts12; is created inmediately before.

Is it for the PROC SQL?

Best,

Martín

Re: Error in code to be executed repeatedly

mchiappe83 — Thu, 14 Jun 2018 21:08:12 GMT

Thanks @Kurt_Bremser for your time

Because (as I already explained) the following wrong libname statement clears the libref.
And code appears in the log because you ran it, period.
Pay attention to details and make sure that code and logs are from the same execution.

I'm sorry but, if you look into the code -not the log- (the wrong libname statement that appears in the log) isn't there. Code and logs are from the same execution.

I'd understand if you prefer not to continue with this stuff.

Again, thanks a lot for your time.

Martin

Re: Error in code to be executed repeatedly

Kurt_Bremser — Fri, 15 Jun 2018 06:56:39 GMT

When something unexpected happens, the log is your sole authority in determining what went wrong, as it reflects the code that was actually executed, period. Base(!) SAS does not "invent" phantom statements on its own. So you need to investigate where that erroneus libname statement comes from, and remove it. Until you do that, the code will not work.

But I think I see where this is coming from. Log line 228 (the erroneous libname) is in a code part that seems to be automatically created by DI Studio. This part is inserted between your program code (delta-automotor.sas) lines 67 (which corresponds to log line 193) and 70 (which corresponds to log line 958). So you see there's almost 800 lines of code created that are not part of the code you see.

Maybe one of the DI Studio experts can come up with an idea why that libname appears "out of the blue".

If none of the posters here can come up with an idea that helps, I am afraid (as I do not work with DI Studio, it's not even installed here) I can only refer you to SAS technical support. Make sure you point them to the problem point so they do not need to find it on their own.

As a workaround, you could insert a correct libname statement immediately before code line 70, so that it overrides the wrong one. And you need to investigate which mechanism makes DI Studio insert that additional code. Is it because the code you showed is actually compiled from several nodes in a DI Studio project/flow?