how to insert a new column into dataset use proc sql?

[Mike_Davis]

How do you insert/update a new column into dataset using PROC SQL?

Thanks

[Linlin · Accepted solution]

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;

[Reeza]

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;

[Haikuo]

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)>;

ALTER TABLE table-name <ADD column-definition<, ... column-definition>>

Haikuo

[Mike_Davis]

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

[Howles]

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