SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

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

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 7
Accepted Solution

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

[ Edited ]

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.


Accepted Solutions
Solution
‎07-07-2016 04:38 AM
Respected Advisor
Posts: 3,887

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

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;

 

 

View solution in original post


All Replies
Regular Contributor
Posts: 241

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

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

 

If you are okay with warning then go ahead.

Occasional Contributor
Posts: 7

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

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...

Solution
‎07-07-2016 04:38 AM
Respected Advisor
Posts: 3,887

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

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;

 

 

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 308 views
  • 1 like
  • 3 in conversation