salary float datatype in proc sql

[BrahmanandaRao]

proc sql;
create table employeesdata
    (employee_id         num,
    first_name           char(15) ,
    last_name            char(18),  
    email                char(18) ,
    phone_number         num(20)    ,
    hire_date            date format=date9., 
    job_id               char(12) ,     
    salary               decimal(12,2),  
    commission_pct       num format=16.2, 
    manager_id           num,  
    department_id        num);

insert into employeesdata values (100  'Steven '      'King'         'SKING'     515.123.4567        '2003-06-17'  'ADPRES'     24000.00            0.00           0             90 );
insert into employeesdata values (101 ' Neena'        'Kochhar'      'NKOCHHAR'  515.123.4568        '2005-09-21'  'ADVP'       17000.00            0.00         100             90 );
insert into employeesdata values (102  'Lex'          'De Haan'      'LDEHAAN'   515.123.4569        '2001-01-13'  'ADVP'       17000.00            0.00         100             90 );
insert into employeesdata values (103  'Alexander'    'Hunold'       'AHUNOLD'   590.423.4567        '2006-01-03'  'ITPROG'      9000.00            0.00         102             60 );
insert into employeesdata values (104  'Bruce'        'Ernst'        'BERNST'    590.423.4568        '2007-05-21'  'ITPROG'      6000.00            0.00         103             60 );
insert into employeesdata values (105  'David'        'Austin'       'DAUSTIN'   590.423.4569        '2005-06-25'  'ITPROG'      4800.00            0.00         103             60 );
insert into employeesdata values (106  'Valli'        'Pataballa'    'VPATABAL'  590.423.4560        '2006-02-05'  'ITPROG'      4800.00            0.00         103             60 );

Table created  why it shows error

ERROR: VALUES clause 1 attempts to insert more columns than specified after the INSERT
table name.
ERROR: Value 8 of VALUES clause 1 does not match the data type of the corresponding
column in the object-item list (in the SELECT clause).
490 insert into employeesdata values (101 ' Neena' 'Kochhar' 'NKOCHHAR'
490! 515.123.4568 '2005-09-21' 'ADVP' 17000.00 0.00 100
490! 90 );
ERROR: VALUES clause 1 attempts to insert more columns than specified after the INSERT
table name.
ERROR: Value 8 of VALUES clause 1 does not match the data type of the corresponding
column in the object-item list (in the SELECT clause).

[Tom]

A phone number is NOT a number, so the variable (aka column in SQL speak) is defined wrong, or named wrong.

515.123.4567 is also NOT a number.

I would normally use commas between the items in a VALUES statement (SQL really likes commas, on of the reasons it is such a pain to use).  I was surprised that it allowed the values to be separated by blank space instead.

So part of the confusion is that it is seeing 

515.123

as one value to insert and

.4567

as the next value in the list.

Try this example:

proc sql;
create table class like sashelp.class(keep=name age weight);
insert into class values ('X' 123.456.789);
select * from class;
quit;

Name           Age    Weight

X          123.456     0.789

[Kurt_Bremser]

Hasn't this already been dealt with in https://communities.sas.com/t5/SAS-Procedures/insert-values-in-proc-sql/m-p/596038 ?

[Kurt_Bremser]

And my advice from your previous post stands: use the much simpler data step:

data employeesdata;
input
  employee_id :$3.
  first_name :$15.
  last_name :&$18.
  email :$18.
  phone_number :$20.
  hire_date :yymmdd10.
  job_id :$12.
  salary
  commission_pct
  manager_id :$3.
  department_id :$3.
;
format
  hire_date yymmddd10.
  salary 12.2
  commission_pct 7.2
;
datalines;
100  Steven  King  SKING 515.123.4567 2003-06-17 ADPRES 24000.00 0.00 0 90 
101  Neena  Kochhar  NKOCHHAR 515.123.4568 2005-09-21 ADVP 17000.00 0.00 100 90 
102  Lex  De Haan  LDEHAAN 515.123.4569 2001-01-13 ADVP 17000.00 0.00 100 90 
103  Alexander  Hunold  AHUNOLD 590.423.4567 2006-01-03 ITPROG 9000.00 0.00 102 60 
104  Bruce  Ernst  BERNST 590.423.4568 2007-05-21 ITPROG 6000.00 0.00 103 60 
105  David  Austin  DAUSTIN 590.423.4569 2005-06-25 ITPROG 4800.00 0.00 103 60 
106  Valli  Pataballa  VPATABAL 590.423.4560 2006-02-05 ITPROG 4800.00 0.00 103 60
;

[BrahmanandaRao]

Hi sir,

i want proc sql 

output 

[Kurt_Bremser]

---

@BrahmanandaRao wrote:

Hi sir,

i want proc sql 

output 

---

Maxim 14.

[Kurt_Bremser]

And only store values as numbers that will/can be used for calculations. Wasting 8 numeric bytes for a three-character ID is nonsense.

And if you have longer ID's, numeric will sooner or later have precision issues, and it can't deal correctly with leading zeroes.