topic Re: how to insert a new colomn into dataset use proc sql? in SAS Procedures

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

Mike_Davis — Mon, 31 Oct 2016 16:48:57 GMT

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

Thanks

Linlin — Mon, 31 Oct 2016 16:52:56 GMT

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;

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 — Tue, 08 May 2012 20:13:49 GMT

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 — Tue, 08 May 2012 20:16:16 GMT

if you are asking to add (insert) columns to the existing table, take a look at this page:

PROC SQL <option(s)>;

ALTER TABLE table-name

Haikuo

Mike_Davis — Wed, 09 May 2012 13:36:34 GMT

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 — Fri, 01 Jun 2012 21:07:57 GMT

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