Script creates file on second attempt of running

pedrammobedi — Mon, 12 Sep 2016 14:30:37 GMT

I am relatively new to SAS Base. I have been wanting to write a script that does the following:

Using a predefined ODBC connection, create a table based on the result of a simple SELECT query. The result is copied in the WORK library
Copy the result of the above query (which is now a SAS dataset file in the temporary WORK folder) to a new destination.

This is my script (I have hidden the connection string and the path to destination folder):

LIBNAME DataMart ODBC noprompt= "MY_CONNECTION_STRING";
LIBNAME Autoload "PATH_TO_DESTINATION_FOLDER";
DATA NEW;
SET DataMart.ReportLG;
RUN;
PROC SQL;
CREATE TABLE ReportLG AS SELECT * FROM DataMart.ReportLG;
QUIT;

DATA autoload.ReportLG;
SET ReportLG;

When I commit this code in SAS Base, it is only with my second attempt that I see the result file showing up in PATH_TO_DESTINATION_FOLDER.

Since I need to schedule this script so that the data remains updated, I need to know why the first commit does not do the job. What is it that I am doing wrong?

This is the log I get in SAS Base with the first commit:

1    LIBNAME DataMart ODBC noprompt= XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX;

NOTE: Libref DATAMART was successfully assigned as follows:
      Engine:        ODBC
      Physical Name: actionbase
2    LIBNAME Autoload
2  ! "PATH_TO_DESTINATION_FOLDER";
NOTE: Libref AUTOLOAD was successfully assigned as follows:
      Engine:        V9
      Physical Name:
      PATH_TO_DESTINATION_FOLDER
3    DATA NEW;
4    SET DataMart.ReportLG;
5    RUN;

NOTE: There were 44 observations read from the data set DATAMART.ReportLG.
NOTE: The data set WORK.NEW has 44 observations and 33 variables.
NOTE: DATA statement used (Total process time):
      real time           0.02 seconds
      cpu time            0.01 seconds


6    PROC SQL;
7    CREATE TABLE ReportLG AS SELECT * FROM DataMart.ReportLG;
NOTE: Table WORK.REPORTLG created, with 44 rows and 33 columns.

8    QUIT;
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.02 seconds
      cpu time            0.01 seconds


9
10   DATA autoload.ReportLG;
11   SET ReportLG;

And this is the log for the second commit which causes the file to be created:

12   LIBNAME DataMart ODBC noprompt= XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX;

NOTE: Libref DATAMART was successfully assigned as follows:
      Engine:        ODBC
      Physical Name: actionbase
13   LIBNAME Autoload
13 ! "PATH_TO_DESTINATION_FOLDER";
NOTE: Libref AUTOLOAD was successfully assigned as follows:
      Engine:        V9
      Physical Name:
      PATH_TO_DESTINATION_FOLDER

NOTE: There were 44 observations read from the data set WORK.REPORTLG.
NOTE: The data set AUTOLOAD.REPORTLG has 44 observations and 33 variables.
NOTE: DATA statement used (Total process time):
      real time           2:46.00
      cpu time            0.40 seconds


14   DATA NEW;
15   SET DataMart.ReportLG;
16   RUN;

NOTE: There were 44 observations read from the data set DATAMART.ReportLG.
NOTE: The data set WORK.NEW has 44 observations and 33 variables.
NOTE: DATA statement used (Total process time):
      real time           0.04 seconds
      cpu time            0.00 seconds


17   PROC SQL;
18   CREATE TABLE ReportLG AS SELECT * FROM DataMart.ReportLG;
NOTE: Table WORK.REPORTLG created, with 44 rows and 33 columns.

19   QUIT;
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.02 seconds
      cpu time            0.03 seconds


20
21   DATA autoload.ReportLG;
22   SET ReportLG;

Re: Script creates file on second attempt of running

Astounding — Mon, 12 Sep 2016 14:24:52 GMT

You need to add a statement at the end of the program to get that last DATA step to run immediately:

RUN;

Re: Script creates file on second attempt of running

ballardw — Mon, 12 Sep 2016 14:25:27 GMT

Did you forget a RUN at the last data step?

If the last procedure or data step code block does not have an explicit end then it is waiting for the next code to run that has a start of a procedure to terminate the code.

So the appearance after the second run is because the at the start of the second the first code actually detected the step boundary from the Data New.

Re: Script creates file on second attempt of running

pedrammobedi — Mon, 12 Sep 2016 14:34:22 GMT

Thank you so much!

topic Re: Script creates file on second attempt of running in SAS Programming

Script creates file on second attempt of running

Re: Script creates file on second attempt of running

Re: Script creates file on second attempt of running

Re: Script creates file on second attempt of running