insert values in proc sql

[BrahmanandaRao]

proc sql;
create table employeesdata
(employee_id         num (3),
first_name           char(15) ,
last_name            char(18), 	
email                char(18) ,
phone_number         num(18)    ,
hire_date            date	,	
job_id               num(9)   ,  		
salary               num(15)  , 	
commission_pct       num(8)	,	
manager_id     	     num(8),	
department_id        num(8));

insert into employeesdata values(100 ,'steven', 'king' ,'sking' ,515.123.4567 ,17-06-2003,	'adpres'	,24000, 0, 0 ,90);

quit;

WARNING: The quoted string currently being processed has become more than 262 characters
long. You might have unbalanced quotation marks.
769 last_name char(18),
770 email char(18) ,
771 phone_number num(18) ,
772 hire_date date ,
773 job_id num(9) ,
774 salary num(15) ,
762 insert into employeesdata values(100 ,'steven', 'king' ,'sking' ,'515.123.4567'
762! ,17-06-2003, 'adpres' ,24000, 0, 0 ,90);
--------------------
49
NOTE 49-169: The meaning of an identifier after a quoted string might change in a future
SAS release. Inserting white space between a quoted string and the
succeeding identifier is recommended.

[Kurt_Bremser]

You most probably had unbalanced quotes further up in the code. Restart the SAS session to clean that up.

[BrahmanandaRao]

Hi sir 

i restart the session and run again but still i got  error like below

NOTE: Table WORK.EMPLOYEESDATA created, with 0 rows and 11 columns.
14
15 insert into employeesdata values(100 ,'steven', 'king' ,'sking' ,515.123.4567
15 ! ,17-06-2003, 'adpres' ,24000, 0, 0 ,90);
ERROR: VALUES clause 1 attempts to insert more columns than specified after the INSERT
table name.
E

NOTE: Table WORK.EMPLOYEESDATA created, with 0 rows and 11 columns.
14
15 insert into employeesdata values(100 ,'steven', 'king' ,'sking' ,515.123.4567
15 ! ,17-06-2003, 'adpres' ,24000, 0, 0 ,90);
ERROR: VALUES clause 1 attempts to insert more columns than specified after the INSERT
table name.
ERROR: Value 10 of VALUES clause 1 does not match the data type of the corresponding
column in the object-item list (in the SELECT clause).
16
17 quit;
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE SQL used (Total process time):
real time 3.59 seconds
cpu time 0.09 seconds

RROR: Value 10 of VALUES clause 1 does not match the data type of the corresponding
column in the object-item list (in the SELECT clause).
16
17 quit;
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE SQL used (Total process time):
real time 3.59 seconds
cpu time 0.09 seconds

[Kurt_Bremser]

515.123.4567

is not a valid number. Numbers can only have one decimal period.

Since, from your previous post, this column is a phone number, I strongly suggest to store it as character.

17-06-2003

is not a valid SAS date value. To supply that date literal correctly, use

'17jun2003'd

[Patrick · Accepted solution]

@BrahmanandaRao 

That's because the values you're trying to insert are invalid for the data types in your source table

This is what you're using

  insert into employeesdata values(100 ,'steven', 'king' ,'sking' ,515.123.4567 ,'17-06-2003',  'adpres'  ,24000, 0, 0 ,90);

The values which are invalid:

515.123.4567  - This must be a string which SAS can interpret as a numerical value. The dot is the decimal separator and you can only have one of these.I assume you're using the dot as thousand separator. If so then the value you need to pass is: 5151234567

17-06-2003 - You need to use a SAS date value. One way to go: '17JUN2003'd

'adpres' - You defined column job_id as num(9) so you can't use a string here.

Not throwing an error but still to be considered.

- Unlike for most databases length for a numerical SAS data type doesn't mean the number of digits stored but how much bytes SAS uses to store a number. You would define this with a LENGTH statement. Providing a definition via NUM(n) appears to be simply ignored when creating a SAS table and a variable with length of 8 bytes gets created (which is actually a good thing).

For DATE variables: You probably want to define a format so that the SAS date values become human readable for printing.

Amending the code you've posted below will work.

proc sql;
  create table employeesdata
    (employee_id         num,
    first_name           char(15) ,
    last_name            char(18),  
    email                char(18) ,
    phone_number         num(18)    ,
    hire_date            date format=date9., 
    job_id               num   ,     
    salary               num  ,   
    commission_pct       num , 
    manager_id           num,  
    department_id        num
    )
    ;
  insert into employeesdata values(100 ,'steven', 'king' ,'sking' ,515.123 ,'17jun2003'd,  123  ,24000, 0, 0 ,90);
quit;

[Kurt_Bremser]

And if your goal is to create a dataset from literals, the data step is the way to go:

data employeesdata;
input
  employee_id :$3.
  first_name :$15.
  last_name :$18.
  email :$18.
  phone_number :$18.
  hire_date :ddmmyy10.	
  job_id :$9.  		
  salary	
  commission_pct
  manager_id :$8.
  department_id :$8.
;
format hire_date ddmmyyd10.;
datalines;
100 steven king sking 515.123.4567 17-06-2003 adpres 24000 0 0 90
;

It is much easier to add additional datalines than insert statements in SQL.

Edit: changed the last two columns to character, as they obviously contain ID values. Set the correct length as per data specifications.

[BrahmanandaRao]

sir 

suppose i want decimals in 

commission_pct(0.00)

insert into employeesdata values(

100 steven king sking 515.123.4567 17-06-2003 adpres 24000 0.00 0 90

 commission_pct  num(3 , 6)not working 

how to solve it 

[Patrick]

@BrahmanandaRao 

The one thing is how SAS stores values internally. For this you just need to pass in a valid string (and 0.00 is valid), the other thing is how SAS prints the values if you don't define an explicit format. If you want anything else than the SAS default for numerical variables (which I believe is BEST32.) then define the format explicitly for the variable when creating the table. This makes the format an attribute of the column and SAS will use it whenever you "look" at the data without defining another format explicitly.

proc sql;
  create table employeesdata
    (employee_id         num,
    first_name           char(15) ,
    last_name            char(18),  
    email                char(18) ,
    phone_number         num(18)    ,
    hire_date            date format=date9., 
    job_id               num   ,     
    salary               num  ,   
    commission_pct       num format=16.2, 
    manager_id           num,  
    department_id        num
    )
    ;
  insert into employeesdata values(100 ,'steven', 'king' ,'sking' ,515.123 ,'17jun2003'd,  123  ,24000, 0.05, 0 ,90);
  insert into employeesdata values(100 ,'steven', 'king' ,'sking' ,515.123 ,'17jun2003'd,  123  ,24000, 0, 0 ,90);
quit;
proc print data=employeesdata;
run;

[Tom]

---

@BrahmanandaRao wrote:

sir 

suppose i want decimals in 

commission_pct(0.00)

insert into employeesdata values(

100 steven king sking 515.123.4567 17-06-2003 adpres 24000 0.00 0 90

 commission_pct  num(3 , 6)not working 

how to solve it 

---

It is not at all clear what you are asking.

Remember that SAS stores only two types of variables, fixed length character strings and floating point numbers. You can use any valid number representation in the VALUES() list, the same as you would use in any SAS expression.  Character values must be enclosed in quotes.  SAS stores dates as numbers, so to insert a date either enter the actual number of days SAS uses for that date, or use a date literal which is a quoted string in a style that the DATE informat can read with the letter D appended after the closing quote, like '17jun2003'd.

If you want the change how the values are DISPLAYed then attach a FORMAT to the variable. So use a date type format for your date values. You could use the 4.2 format to display percentages with two decimal places.