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.
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;
You can modify the existing table but you would get warning in logs.
If you are okay with warning then go ahead.
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...
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.
Find more tutorials on the SAS Users YouTube channel.