topic PROC SQL - How to create a table, i am selecting in the same step? in SAS Data Management

PROC SQL - How to create a table, i am selecting in the same step?

Felix_ — Wed, 06 Jul 2016 13:17:24 GMT

Hello SAS Community.

I have a hopefully easier problem to solve for you:

PROC SQL;

CREATE TABLE rawdat.detail AS
	SELECT a.*
	FROM rawdat.detail  AS a INNER JOIN
     	rawdat.product AS B 
     	ON a.case_id = b.case_id AND a.seq_id = b.id
	ORDER BY x, y, z;

QUIT;

My Problem is, that i wand to modify the table i am reading in.

I could solve the problem with creating another table, but thats not what i am looking for.

Re: PROC SQL - How to create a table, i am selecting in the same step?

RahulG — Wed, 06 Jul 2016 16:06:12 GMT

You can modify the existing table but you would get warning in logs.

If you are okay with warning then go ahead.

Re: PROC SQL - How to create a table, i am selecting in the same step?

Felix_ — Thu, 07 Jul 2016 07:15:23 GMT

Hello RahulG, thanks for the response.
The warning in the log-file is exactly my problem. I want to have a fast solution (with sql) with a clean log. Couldn't find the solution so far...

Re: PROC SQL - How to create a table, i am selecting in the same step?

Patrick — Thu, 07 Jul 2016 07:33:53 GMT

Create table A ... from table A ... is simply not valid SQL - so don't do it.

Use a Delete instead "delete .... where not exists (...)" as this is actually the result of your inner join.

As you can't re-create the same table with a SQL you also can't resort it directly in-itself. You could create an index instead.

Or -alternatively - you could do something as below

PROC SQL;
  CREATE view V_detail AS
    SELECT a.*
      FROM rawdat.detail  AS a 
        INNER JOIN
          rawdat.product AS B       
          ON a.case_id = b.case_id AND a.seq_id = b.id  
          ;
QUIT;

proc sort data=V_detail out=rawdat.detail;
  by x y z;
run;