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
Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.
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.
Ready to level-up your skills? Choose your own adventure.