BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
pedrammobedi
Quartz | Level 8

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

 

  1. 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
  2. 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;
1 ACCEPTED SOLUTION

Accepted Solutions
ballardw
Super User

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.

View solution in original post

3 REPLIES 3
Astounding
PROC Star

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

 

RUN;

ballardw
Super User

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.

pedrammobedi
Quartz | Level 8
Thank you so much!

sas-innovate-2024.png

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.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 3 replies
  • 1019 views
  • 6 likes
  • 3 in conversation