topic Re: Remote tables that look like they have data in them but don't - What to do? in SAS Programming

Remote tables that look like they have data in them but don't - What to do?

Chris_L — Mon, 26 Sep 2016 15:13:22 GMT

I have never seen this before while using RSUBMIT.

I run code using remote submit and all is fine.

Then if I run a piece of code and there is an error, such as:

NOTE: Remote submit to ROMOTE complete.
85
86   rsubmit;
NOTE: Remote submit to ROMOTE commencing.
216  data all_data1;
217      merge out_1 (in=a)
218            rank_ons_vis_summ2 (in=b);
ERROR: File WORK.RANK_ONS_VIS_SUMM2.DATA does not exist.
219      by postcode_sector;
220      if a;
221  run;

NOTE: The SAS System stopped processing this step because of errors.
NOTE: SAS set option OBS=0 and will continue to check statements.
      This may cause NOTE: No observations in data set.
WARNING: The data set WORK.ALL_DATA1 may be incomplete.  When this step was stopped there were 0
         observations and 93 variables.

From this point on all the tables will open and show data in them, but if I run anything else, SAS will come back as though the table is empty. For example the table OUT_1 has data in it, but if I run code after an error I'll get:

88   rsubmit;
NOTE: Remote submit to ROMOTE commencing.
231  data out_1f ;
232   set out_1;
233
234  run;

NOTE: The data set WORK.OUT_1F has 0 observations and 93 variables.
WARNING: Data set WORK.OUT_1F was not replaced because this step was stopped.
NOTE: DATA statement used (Total process time):
      real time           0.03 seconds
      user cpu time       0.00 seconds
      system cpu time     0.00 seconds
      Memory                            305k
      OS Memory                         8996k
      Timestamp            9/26/2016  3:58:10 PM


NOTE: Remote submit to ROMOTE complete.

The only way that I can reset this is to shut down SAS and start it up again.

What else can I do??

Re: Remote tables that look like they have data in them but don't - What to do?

ballardw — Mon, 26 Sep 2016 15:32:38 GMT

What LIBRARY name did you assign to the remote connection?

This code:

216 data all_data1;
217 merge out_1 (in=a)
218 rank_ons_vis_summ2 (in=b); <==== No library name so is referencing you local WORK library, if this is supposed to be the remote then preface with the library name such as ROMOTE.ran_on_vis_summ2

Re: Remote tables that look like they have data in them but don't - What to do?

Chris_L — Mon, 26 Sep 2016 16:14:11 GMT

Thanks for that

The library name is not realy the issue here.

The problem is that after the error is reported in SAS, ALL tables act as though they have no data in them from that point onward.

So in the above case, even if I do point SAS in the right direction for the table rank_ons_vis_summ2, it will still come back with the comment that there is 0 observations.

If, however, I shut down SAS and start it up again, the code will run fine.

Re: Remote tables that look like they have data in them but don't - What to do?

ballardw — Mon, 26 Sep 2016 16:22:38 GMT

I suggest that you post the details on how you are connecting and possibly more of an example connection. Replace sensitive info like user or password with xxx or similar but the connect strings and code uptil the first error.

You database admin may also have some settings related to this.

Re: Remote tables that look like they have data in them but don't - What to do?

SASKiwi — Tue, 27 Sep 2016 06:36:07 GMT

Looks like your remote SAS session has the SAS option SYNTAXCHECK in effect. Specify options NOSYTAXCHECK to switch off this behaviour:

http://support.sas.com/documentation/cdl/en/lesysoptsref/68023/HTML/default/viewer.htm#n014qbvh3po8w5n1qlqbzr22vtg0.htm

Re: Remote tables that look like they have data in them but don't - What to do?

Chris_L — Tue, 27 Sep 2016 08:11:18 GMT

Thanks for that

That worked fine!