topic Re: PROC SQL CREATE TABLE in SAS Procedures

PROC SQL CREATE TABLE

cmcooper11 — Thu, 18 Jul 2019 23:52:58 GMT

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!

Re: PROC SQL CREATE TABLE

Reeza — Fri, 19 Jul 2019 00:25:25 GMT

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!

Re: PROC SQL CREATE TABLE

cmcooper11 — Fri, 19 Jul 2019 00:58:23 GMT

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.

Re: PROC SQL CREATE TABLE

Reeza — Fri, 19 Jul 2019 00:59:30 GMT

You're trying to view results that may be 100 million rows? Yeah, just opening that table may make EG crash. Instead print out small subsets.

Re: PROC SQL CREATE TABLE

cmcooper11 — Fri, 19 Jul 2019 01:06:04 GMT

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.

Re: PROC SQL CREATE TABLE

Reeza — Fri, 19 Jul 2019 01:07:42 GMT

Well, if it was still running the progress bar would show that. Are you keeping on eye on that? It's usually at the bottom of the IDE.

For viewing a subset you can either create a small subset and view it or use PROC PRINT.

data temp;
set bigTable (obs=100);
run;

proc print data=BigTable (obs=100);
run;

Re: PROC SQL CREATE TABLE

cmcooper11 — Fri, 19 Jul 2019 01:13:12 GMT

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.

Re: PROC SQL CREATE TABLE

Reeza — Fri, 19 Jul 2019 01:17:12 GMT

Can you consider appending them instead via append and using format or something else to determine var3?

Re: PROC SQL CREATE TABLE

cmcooper11 — Fri, 19 Jul 2019 01:20:21 GMT

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?

Re: PROC SQL CREATE TABLE

Reeza — Fri, 19 Jul 2019 01:28:36 GMT

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?

Re: PROC SQL CREATE TABLE

cmcooper11 — Fri, 19 Jul 2019 01:35:22 GMT

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?

Re: PROC SQL CREATE TABLE

Reeza — Fri, 19 Jul 2019 01:39:47 GMT

PROCs are different to SQL and optimized for smaller tasks that are done quite often. APPEND will only union the two files, you'll need a second step to create the new variable but I do think a format would work for whatever you're doing.

You can definitely get away with just using SQL in SAS but it's not an efficient method by any means.

Re: PROC SQL CREATE TABLE

cmcooper11 — Fri, 19 Jul 2019 01:48:32 GMT

This has been very insightful. I will apply your suggestions and update tomorrow.