PROC SQL IN SAS

Question

Can you write a code in Proc SQL in SAS where we have to add a new column named StudentName to Student table which can be blank

Can you provide a example scenario

Like what are the steps to do that in Proc SQL and how to write a code.

Answer 1

I have updated your question to include the code you provided in comments and the error message that I get when I run that code.

In future, when writing questions on StackOverflow, it is more helpful if you include the full code AND any error messages in your question, that way people can help point out the specific problem and help you understand better.

I must admit the SAS documentation for an entry-level user is not the easiest to follow and the specific page for ALTER TABLE does not include any examples. However, I did find some here, in the SAS Docs:

• SAS SQL Procedure User’s Guide
  • Using the SQL Procedure
    • Creating and Updating Tables and Views
      • Altering Columns
        • Adding a Column

So let's look at one of those examples:

proc sql;
   alter table sql.newcountries
      add density num label='Population Density' format=6.2;
quit;

There's a bit more than you need here, but let's go through each component:

• add - tells PROC SQL you want to add a column
• density - the name of your column
• num - the data type of your column, in SAS this is either (char or num)
• label= - the label for your column
• format= - the format for your column

To update your code, you need to do a couple of things:

• Switch around the variable name and the type
• Update student name so it has no spaces, you can do this using an underscore student_name

proc sql; 
  alter table students add student_name char;
quit;

The resulting log:

 70           alter table students add student_name char;
 NOTE: Table WORK.STUDENTS has been modified, with 2 columns.

The problem you might now encounter is that SAS initialises all columns with a length of 8, so to specify a longer length, use length=:

proc sql;
  alter table students add long_student_name char length=200;
quit;

If you've read this far and this has helped, please remember to bear in mind my tips about including more information in your question - you are far more likely to receive a good quality answer that way.