topic Re: salary float datatype in proc sql in SAS Procedures

salary float datatype in proc sql

BrahmanandaRao — Wed, 16 Oct 2019 04:47:04 GMT

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).

Re: salary float datatype in proc sql

Tom — Wed, 16 Oct 2019 05:17:59 GMT

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

Re: salary float datatype in proc sql

Kurt_Bremser — Wed, 16 Oct 2019 06:48:00 GMT

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

Re: salary float datatype in proc sql

Kurt_Bremser — Wed, 16 Oct 2019 07:02:47 GMT

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
;

Re: salary float datatype in proc sql

Kurt_Bremser — Wed, 16 Oct 2019 07:05:04 GMT

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.

Re: salary float datatype in proc sql

BrahmanandaRao — Wed, 16 Oct 2019 07:28:29 GMT

Hi sir,

i want proc sql

output

Re: salary float datatype in proc sql

Kurt_Bremser — Wed, 16 Oct 2019 07:30:31 GMT

@BrahmanandaRao wrote:

Hi sir,

i want proc sql

output

Maxim 14.