Help using Base SAS procedures

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

Accepted Solution Solved
Reply
Regular Contributor
Posts: 222
Accepted Solution

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

[ Edited ]

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

 

Thanks


Accepted Solutions
Solution
‎10-31-2016 12:53 PM
Super Contributor
Posts: 1,636

Re: how to insert a new colomn into dataset use proc sql?

[ Edited ]

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;

 

View solution in original post


All Replies
Solution
‎10-31-2016 12:53 PM
Super Contributor
Posts: 1,636

Re: how to insert a new colomn into dataset use proc sql?

[ Edited ]

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;

 

Super User
Posts: 17,784

Re: how to insert a new colomn into dataset use proc sql?

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;

Respected Advisor
Posts: 3,124

Re: how to insert a new colomn into dataset use proc sql?

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

Regular Contributor
Posts: 222

Re: how to insert a new colomn into dataset use proc sql?


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

Regular Contributor
Posts: 184

Re: how to insert a new colomn into dataset use proc sql?

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

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 47249 views
  • 1 like
  • 5 in conversation