topic Re: Data truncated when database updated in SAS Data Management

Data truncated when database updated

Betsy — Tue, 15 Sep 2020 15:09:50 GMT

Hi all,

Sorry for the length of this question.

I have about 20 datasets that I cleaned in their own files. I merged them in a separate file, and had no issues with the database at that time. The process was 1. save completed individual datasets to a library 2. in a separate file, sort and merge all datasets.

I updated individual datasets several times and re-ran the merge to update the database without issue.

However, this most recent update, I the dataset I updated was 73 observations. When I re-ran the merge, half the datasets were truncated to 73 observations. I had to re-run each dataset individually and then merge again. I didn't change any code, either before or after the error; it was corrected just by re-running.

I am wondering if anyone has experience with similar issues. I'd like to avoid this happening again, as it's time consuming and I might not always realize there's missing data right away.

Re: Data truncated when database updated

ballardw — Tue, 15 Sep 2020 15:51:26 GMT

You would have to show the entire code and likely the LOG of the run that created the incomplete data to provide any relevant answer.

The most likely thing I would expect to be a sort issue meaning that UPDATE or MERGE would fail, but you don't say if you read the logs after the first problem run.

Re: Data truncated when database updated

Kurt_Bremser — Tue, 15 Sep 2020 17:03:22 GMT

In this post: https://communities.sas.com/t5/SAS-Data-Management/Extra-rows-when-importing-Excel-data/m-p/683662/highlight/true#M19554 you used

options obs=73;

which, I guarantee you, caused your problem. See Maxim 50.

Re: Data truncated when database updated

Betsy — Tue, 15 Sep 2020 17:26:05 GMT

Thank you! It appears that command translated to other datasets in separate files, which I had no idea it could do.

Thank you for referencing the maxims, I bookmarked them and found them all extremely helpful.

Re: Data truncated when database updated

Kurt_Bremser — Tue, 15 Sep 2020 18:15:43 GMT

System options stay until they are changed, or the SAS session ends. This can make them quite dangerous to use without proper diligence 😉