SAS Studio Program: append dataset data step works manually, but not work after scheduled as a job

[ly2024]

Hello community,

I encountered an issue and needed the help of experts to see what happened.

I have a SAS Studio program scheduled to run a job daily. It worked well. On Wednesday, I updated the program a little bit and tested it by running it manually. It worked at that time. Then I rescheduled a job for it. The job failed. The error message is "ERROR: The provided list of column names did not match names in table XXX" while it is to append a dataset to CAS table.

SAS code is as below:

DATA CASLIBNAME.XXX (APPEND=YES);
SET XXX_DAILY;
RUN;

I just debugged and ran it manually, and it works. 

May I know how to resolve it and avoid this kind of thing when trying to append in the future?

Thank you so much!

Lin

[Tom]

Sound like that dataset XXX_DAILY did not have the same structure as the permanent dataset CASLIB.XXX (note libref's cannot be longer than 8 characters).

You need to understand why that is.

Also why are you using a DATA step to append instead of using PROC APPEND?

[ly2024]

Hi Tom,

Thank you for your feedback.

I first ran the code in SAS Base and got the permanent one refreshed with data until Tuesday. Then went to the SAS Studio, updated the code, loaded the permanent one, ran the code, and appended Wednesday's dataset. Maybe there is something different between BASE and Studio. Let me check my BASE program.

If its structure was different from the permanent one's structure, why would the manual run be done successfully?

CASLIBNAME is the name of the CAS folder. But good catch on that length requirement. 

Thank you!

[Tom]

The most common source of structural changes to datasets is the use of PROC IMPORT to make GUESSES about how to create a SAS dataset.

If you have a delimited text file (such as a CSV file) then just write a data step to read the CSV file instead of running PROC IMPORT.  That way the structure will not vary from day to day just because there is a difference in the particular set of lines this day's CSV file has.

[ly2024]

XXX_Daily is a SAS dataset created by the code in the same program. The program will first do some manipulations and after the dataset is done, then append to the CAS table.

[ly2024]

Let me try PROC APPEND! 😊

[ballardw]

If a data step fails because of a structure difference Proc Append is very likely to fail as well.

You didn't answer anything about the actual source/contents of the daily file, just mentioned that the code is in the same program.

Proc Append by default will fail to append when:

A variable in the appending data set is not in the base  

A variable in the appending data set has a different type than a variable of the same name in the base file  (i.e. numeric vs character).

A variable in the appending data set has larger length that a variable of the same name in the base file. A smaller length will generate a warning.

Proc Import is often a cause of these differences as Import will make separate guesses as to the properties of variables or in the case of names and number of variables get those if the source file changes.

Similar if reading from any external data base where the properties/columns of the data files change.

[ly2024]

Ballardw,

Thank you.

Daily and permanent are using the same source. The program codes to prepare them are also the same, except the one running in SAS Studio to prepare daily has one more step to append the daily to the permanent.

I don't understand why it would run successfully when I executed it step by step in Studio, but the automated job will fail.

I am thinking of copying the code into a new program and scheduling a job on that new one to see.

Best,

Lin

[ly2024]

I deleted the original scheduled job to run the Studio program. Then I scheduled a new job to run it. The job will run daily in the morning. Will check on it tomorrow morning. Hopefully this will resovle the issue. 

[ballardw]

Save LOG of the program. Often there are notes or warnings about what goes on in addition to the errors. Those may tell us what to look at.

If any of the sources or targets used are in external databases there may be additional settings needed to discuss. 

I don't use Studio or know your entire environment. I do know that for many years that the default setting can be different between an interactive session and what we used to call "batch" processing. It may be that your schedule behaves somewhat like a batch program and needs one or more system options adjusted to run in the schedule mode.

[ly2024]

Hi Ballardw,

Thank you for your thoughtful suggestions. Yes, I saved the LOG. 

This morning, the job ran successfully. The permanent appends the dataset, and the dashboard shows the update. So when the Studio program has some modification or update, the job scheduled to run it needs to be deleted, and a new job needs to be scheduled. This is what I learned. As you said, this may be related to the system options set up.

If I find out why later, I will update this post.

Thank you.

[ly2024]

This is what I found out:

The old job actually ran the old program, instead of the new program with the update (same name, same saved folder), but the permanent dataset already contains the new columns because of my manual run. So they don't match.

Whenever the program in SAS Studio is modified, the old scheduled job should be deleted, although the program still uses the same name and is saved in the same folder; and a new job for the updated program should be scheduled. It seems that the scheduled job will grab the Studio program and keep it with itself instead of just pointing to the program in Studio.