I am relatively new to SAS Base. I have been wanting to write a script that does the following:
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;
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.
You need to add a statement at the end of the program to get that last DATA step to run immediately:
RUN;
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.
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.