What does the log show? Can you post that?
What happens if you try a proc contents on the data set, right after this:
proc contents data=folder1.table1;
run;
@cmcooper11 wrote:
I am running the following approxmited code:
proc sql; create table folder1.table1 as
select
col1,
col2,
case
when col2 in ( '1', '2') then 'text'
end as col3
from
table2
union all
select
col1,
col2,
case
when col2 in ( '1', '2') then 'text'
end as col3
from
table3
;
quit;
But when table1 appears in folder1 and I try open it through SAS EG by double clicking on the file in the library lost I get the following error:
table1 does not exist. You will need to refresh the task that
created it before it can be opened.
When I set inobs and outobs to ten the table opens just fine.
Does anyone know what might be going on?
Thanks!
I won't have the computer that is running the query until tomorrow morning, but there wasn't anything that stood out to me in the logs. There was some warning/note about being unable to use compression and a warning that an "&" in the case statement then values. I have run similar queries without issues before and those warnings/notes were in those logs too.
I will give the proc contents command a chance tomorrow and let you know. I did try:
proc sql; select * from folder1.table1; quit;
but the SAS EG instance locked up both times that I tried it.
Could it be that I just tried to interact with the file before it was done being written to? I believe that Table2 and Table3 have somewhere around 100 million rows.
I don't think that has been an issue for me before. How do you recommend I view a subset of the table? I'm pretty new to SAS so I apologize if that is a trivial question.
The main reason for my concern is that it seem like my code gets hung during that particular proc sql statement and that the inaccessible table is a sign of a deeper issue that I haven't identified yet.
I did see that is was still running according to the progress bar at the bottom. In the past, I have had the progress bar change from "running program blah on..." to something like "running (ready)", but code had not made any apparent progress in two days. Restarting the code showed that it only took one day to run. Given all of this I have been a little suspicious of the progress bar.
I will give the proc contents command and printing a subset a chance tomorrow and let you know.
I appreciate your help with this.
The majority of my programming in SAS has been using PROC SQL. Could you give me an example of what you mean? Would your example replace the UNION and CASE in first example?
CASE is the part that can't be reproduced within an append.
But this would add the data to table2 instead of create a new output table. For the var3 you could choose to either
proc append base=table2 (keep=(var1 var2)) data=table3(Keep=var1 var2);
run;
When appending like this, SAS copies the data over in blocks, not line by line because it isn't processing anything. You can add the var3 in later or change var2 to show that if needed or there are other options depending on what you need to do next.
@cmcooper11 wrote:
The majority of my programming in SAS has been using PROC SQL. Could you give me an example of what you mean? Would your example replace the UNION and CASE in first example?
So I imagine that using append will be quite a bit faster then?
If table2 is a file, is that file permanently modified?
The case statement could have been replace with a join by putting the case data into a data set, which I would be comfortable doing with my current skills. Is the a PROC command similar to either a SQL case or join?
This has been very insightful. I will apply your suggestions and update tomorrow.
SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.
Ready to level-up your skills? Choose your own adventure.