How do you insert/update a new column into dataset using PROC SQL?
Thanks
It depends.
you can use JOIN to get the column from another dataset or use the code below to insert an empty column:
data have;
input id age;
cards;
1 20
2 30
;
proc sql;
create table want
as select *, ' ' format=$8. as new_col from have;
quit;
proc print;run;
From @Reeza:
If you need to add a new column you can use SQL to alter the table as well - i.e. add a column called grade to the class table:
data class;
set sashelp.class;
run;
proc sql;
alter table class
add grade num label='School Grade' format=6.2;
quit;
It depends.
you can use JOIN to get the column from another dataset or use the code below to insert an empty column:
data have;
input id age;
cards;
1 20
2 30
;
proc sql;
create table want
as select *, ' ' format=$8. as new_col from have;
quit;
proc print;run;
From @Reeza:
If you need to add a new column you can use SQL to alter the table as well - i.e. add a column called grade to the class table:
data class;
set sashelp.class;
run;
proc sql;
alter table class
add grade num label='School Grade' format=6.2;
quit;
If you need to add a new column you can use SQL to alter the table as well
IE add a column called grade to the class table:
data class;
set sashelp.class;
run;
proc sql;
alter table class
add grade num label='School Grade' format=6.2;
quit;
if you are asking to add (insert) columns to the existing table, take a look at this page:
http://support.sas.com/documentation/cdl/en/proc/61895/HTML/default/viewer.htm#a002294523.htm
PROC SQL <option(s)>;
|
Haikuo
Thank you all.
LinLin's method and Reeza and Haikuo's methods all works,
LinLin's method create a new dataset but the other not, except this what is the other main difference?
Thanks
Reeza and Haikuo both use the ALTER statement. I believe that this implicitly causes the creation of a new table to replace the old one.
This can be demonstrated by toggling the REPLACE system option to NOREPLACE before invoking ALTER.
Mike.Davis wrote:
Thank you all.LinLin's method and Reeza and Haikuo's methods all works,
LinLin's method create a new dataset but the other not, except this what is the other main difference?
Thanks
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.