Error when loading data into Postgres table from SAS(Invalid input syntax for integer)

WorkingMan — Fri, 30 Nov 2018 07:32:30 GMT

I am using SAS Data Integration Studio with SCD Type 1 loader to load data into PostGres Table.

First of all, the issue that I am facing is the error message: ERROR: CLI execute error: ERROR: invalid input syntax for integer: "1372.8"; Error while executing the query

I've searched the entire table(170 rows) and there is no data with the value of 1372.8 or even close to it.

The postgres table has a few non-nullable columns and i have made sure all 170 records contain values. There is no record with missing value.

I've also tried changing the table properties of postgres to allow null value for all columns now. I am still getting the same exact error.

However, I am getting this strange error. I have also browse through stackoverflow but it seems like this has nothing to do with SAS but postgres. However, the value that i load into postgres is of same data type, always numeric and i have no clue why is this error prompting.

Log file:

NOTE: Appending WORK.WVVOL13 to NETCAP.MasterAssetRenewable.NOTE: FORCE is specified, so dropping/truncating will occur.NOTE: There were 18 observations read from the data set WORK.WVVOL13.NOTE: 17 observations added.NOTE: The data set NETCAP.MasterAssetRenewable has . observations and 18 variables.ERROR: CLI execute error: ERROR: invalid input syntax for integer: "1372.8";

SCD Type 1 Loader code:

/*==========================================================================* 
 * Step:            SCD Type 1                            A5KQQF32.C20002Y5 * 
 * Transform:       SCD Type 1                                              * 
 * Description:                                                             * 
 *                                                                          * 
 * Source Table:    User Written -                        A5KQQF32.C90001ZV * 
 *                   work.ncp_asset_re_toloadnew                            * 
 * Target Tables:   MasterAssetRenewable -                A5KQQF32.BE0000X2 * 
 *                   netcap.MasterAssetRenewable                            * 
 *                  Cross reference -                     A5KQQF32.C90001ZW * 
 *                   work.MasterAssetRenewable_xref                         * 
 *                  Changed records - work.WVVOJQO        A5KQQF32.C90001ZX * 
 *                  New records - work.WVVOL13            A5KQQF32.C90001ZY * 
 *==========================================================================*/ 

options VALIDVARNAME = ANY VALIDMEMNAME = EXTEND; 

%let transformID = %quote(A5KQQF32.C20002Y5);
%let trans_rc = 0;
%let etls_stepStartTime = %sysfunc(datetime(), datetime20.); 

%let SYSLAST = %nrquote(work."ncp_asset_re_toloadnew"n); 

%macro etls_scd_type_1;

/*---- Delete any pre-existing work tables  ----*/ 
proc datasets lib = work nolist nowarn memtype = (data view);
   delete etls_xref;quit;

proc datasets lib = work nolist nowarn memtype = (data view);
   delete "WVVOJQO"n;quit;

proc datasets lib = work nolist nowarn memtype = (data view);
   delete "WVVOL13"n;quit;

/* Determine if the table exists  */ 
%let etls_tableExist = %eval(%sysfunc(exist(netcap."MasterAssetRenewable"n, DATA)) or 
      %sysfunc(exist(netcap."MasterAssetRenewable"n, VIEW))); 

/*---- Create a new table  ----*/ 
%if (&etls_tableExist eq 0) %then 
%do;  /* if table does not exist  */ 

   %put %str(NOTE: Creating table ...);   data netcap."MasterAssetRenewable"n
           (dbnull = (
                      "Id"n = NO
                      "DetailType"n = NO
                      "DependableFactor"n = NO
                      "StatusType"n = NO
                      "DeclaredGenerationMd"n = NO
                      "ActualGenerationMd"n = NO
                      "BusinessAreaId"n = YES
                      "PmuId"n = YES
                      "PpuId"n = YES
                      "CreatedFromId"n = YES
                      "CustomerName"n = YES
                      "CustomerNumber"n = YES
                      "SsuId"n = YES
                      "FeederNo"n = YES
                      "Lat"n = YES
                      "Lng"n = YES
                      "ReCapacity"n = NO
                      "TargetCommDate"n = NO));      attrib "Id"n length = 8         format = 20.         informat = 20.         label = 'Id'; 
      attrib "DetailType"n length = 8         format = 11.         informat = 11.         label = 'DetailType'; 
      attrib "DependableFactor"n length = 8         label = 'DependableFactor'; 
      attrib "StatusType"n length = 8         format = 11.         informat = 11.         label = 'StatusType'; 
      attrib "DeclaredGenerationMd"n length = 8         label = 'DeclaredGenerationMd'; 
      attrib "ActualGenerationMd"n length = 8         label = 'ActualGenerationMd'; 
      attrib "BusinessAreaId"n length = 8         format = 20.         informat = 20.         label = 'BusinessAreaId'; 
      attrib "PmuId"n length = 8         format = 20.         informat = 20.         label = 'PmuId'; 
      attrib "PpuId"n length = 8         format = 20.         informat = 20.         label = 'PpuId'; 
      attrib "CreatedFromId"n length = 8         format = 20.         informat = 20.         label = 'CreatedFromId'; 
      attrib "CustomerName"n length = $1024         format = $1024.         informat = $1024.         label = 'CustomerName'; 
      attrib "CustomerNumber"n length = $1024         format = $1024.         informat = $1024.         label = 'CustomerNumber'; 
      attrib "SsuId"n length = 8         format = 20.         informat = 20.         label = 'SsuId'; 
      attrib "FeederNo"n length = $1024         format = $1024.         informat = $1024.         label = 'FeederNo'; 
      attrib "Lat"n length = 8         label = 'Lat'; 
      attrib "Lng"n length = 8         label = 'Lng'; 
      attrib "ReCapacity"n length = 8         format = 11.         informat = 11.         label = 'ReCapacity'; 
      attrib "TargetCommDate"n length = 8         format = DATETIME25.6         informat = DATETIME25.6         label = 'TargetCommDate'; 
      call missing(of _all_);      stop;   run;

   %rcSet(&syserr); 

   /*---- Create the indexes for a table  ----*/ 
   %put %str(NOTE: Creating indexes ...);
   %macro etls_createIndexes;
      proc sql; 
         connect to POSTGRES
         ( 
             DATABASE=netcap SERVER="unadevsaswas01.hq.tnb.com.my" AUTHDOMAIN="NetcapAuth" 
         ); 
         reset noprint; 
         execute 
         ( 
            create index "IX_MasterAssetRenewable_PpuId"
               on "MasterAssetRenewable"
                    ("PpuId") 
         ) by POSTGRES; 

         %rcSet(&sqlrc); 

         execute 
         ( 
            create index "IX_MasterAssetRenewable_PmuId"
               on "MasterAssetRenewable"
                    ("PmuId") 
         ) by POSTGRES; 

         %rcSet(&sqlrc); 

         execute 
         ( 
            create index "IX_MasterAssetRenewable_CreatedFromId"
               on "MasterAssetRenewable"
                    ("CreatedFromId") 
         ) by POSTGRES; 

         %rcSet(&sqlrc); 

         execute 
         ( 
            create index "IX_MasterAssetRenewable_BusinessAreaId"
               on "MasterAssetRenewable"
                    ("BusinessAreaId") 
         ) by POSTGRES; 

         %rcSet(&sqlrc); 

         execute 
         ( 
            create index "IX_MasterAssetRenewable_SsuId"
               on "MasterAssetRenewable"
                    ("SsuId") 
         ) by POSTGRES; 

         %rcSet(&sqlrc); 

         disconnect from POSTGRES; 
      quit; 

      %rcSet(&sqlrc); 

   %mend etls_createIndexes;
   %etls_createIndexes;

   /*---- Create the integrity constraints for a table  ----*/ 
   %put %str(NOTE: Creating integrity constraints ...);
   proc sql;
      connect to POSTGRES
      ( 
          DATABASE=netcap SERVER="unadevsaswas01.hq.tnb.com.my" AUTHDOMAIN="NetcapAuth" 
      ); 
      reset noprint; 

      execute 
      ( 
         alter table "MasterAssetRenewable"      
            add primary key ("Id")
      ) by POSTGRES; 

      %rcSet(&sqlrc); 

      execute 
      ( 
         alter table "MasterAssetRenewable"      
            add foreign key ("PmuId") references "MasterAssetPmu"
      ) by POSTGRES; 

      %rcSet(&sqlrc); 

      execute 
      ( 
         alter table "MasterAssetRenewable"      
            add foreign key ("PpuId") references "MasterAssetPpu"
      ) by POSTGRES; 

      %rcSet(&sqlrc); 

      disconnect from POSTGRES; 
   quit;

   %rcSet(&sqlrc); 

%end;  /* if table does not exist  */ 

/* Determine if the table exists  */ 
%let etls_tableExist = %eval(%sysfunc(exist(work."MasterAssetRenewable_xref"n, DATA)) or 
      %sysfunc(exist(work."MasterAssetRenewable_xref"n, VIEW))); 

/*---- Create a new table  ----*/ 
%if (&etls_tableExist eq 0) %then 
%do;  /* if table does not exist  */ 

   %put %str(NOTE: Creating table ...);   data work."MasterAssetRenewable_xref"n;      attrib "Id"n length = 8         format = 20.         informat = 20.         label = 'Id'; 
      attrib "compare_digest"n length = $32         format = $32.         informat = $32.; 
      call missing(of _all_);      stop;   run;

   %rcSet(&syserr); 

   /*---- Create the integrity constraints for a table  ----*/ 
   %put %str(NOTE: Creating integrity constraints ...);
   proc datasets library=work nolist;      modify "MasterAssetRenewable_xref"n;         ic create not null ("Id"n);   quit;

   %rcSet(&syserr); 

%end;  /* if table does not exist  */ 

proc datasets lib = work nolist nowarn memtype = (data view);
   delete W159RVJL;quit;

proc sql;
   create view work.W159RVJL as
      select
         "Id"n   
            format = 20.            informat = 20.,
         "DetailType"n,
         "DependableFactor"n,
         "StatusType"n,
         "DeclaredGenerationMd"n,
         "ActualGenerationMd"n,
         "BusinessAreaId"n,
         "PmuId"n   
            format = 20.            informat = 20.,
         "PpuId"n   
            format = 20.            informat = 20.,
         "CreatedFromId"n,
         "CustomerName"n length = 1024   
            format = $1024.            informat = $1024.,
         "CustomerNumber"n length = 1024   
            format = $1024.            informat = $1024.,
         "SsuId"n,
         "FeederNo"n length = 1024   
            format = $1024.            informat = $1024.,
         "Lat"n,
         "Lng"n,
         "ReCapacity"n   
            format = 11.            informat = 11.,
         "TargetCommDate"n   
            format = DATETIME25.6            informat = DATETIME25.6
   from work."ncp_asset_re_toloadnew"n
   ;quit;

%let SYSLAST = work.W159RVJL;

/* create work xref table  */ 
data work.etls_xref(keep = Id compare_digest);   length compare_digest $ 32;

   set netcap."MasterAssetRenewable"n( keep = Id ReCapacity FeederNo DeclaredGenerationMd);

   /* create digest - version 2.1  */ 
   compare_digest = put(md5(catq(' ', ReCapacity, FeederNo, DeclaredGenerationMd)), $hex32.);run;
%rcSet(&syscc); 

/*---- SCD Type 1 processing: hash lookup method  ----*/ 
data 
   /* Changed Records Table  */ 
   work."WVVOJQO"n( keep = Id ReCapacity FeederNo DeclaredGenerationMd compare_digest)

   /* New Records Table  */ 
   work."WVVOL13"n;

   drop source_digest;   length source_digest $ 32;

   if 0 then
      set work.etls_xref;

   if _N_ eq 1 then   do;
      declare hash hct(dataset: 'work.etls_xref', hashexp: 10);                   hct.defineKey("Id");                   hct.defineData("Id", "compare_digest");                   hct.defineDone();
   end;

   set work.W159RVJL;

   /* create digest - version 2.1  */ 
   source_digest = put(md5(catq(' ', ReCapacity, FeederNo, DeclaredGenerationMd)), $hex32.);

   /* match  */ 
   if hct.find() eq 0 then   do;
      /* scd type 1 records  */ 
      if source_digest ne compare_digest then      do;         compare_digest = source_digest;         output work."WVVOJQO"n;
      end;
   end;
   /* new records  */ 
   else if hct.find() ne 0 then   do;      compare_digest = source_digest;      output work."WVVOL13"n;
   end;run;
%rcSet(&syscc); 

/* changed records table - record check  */ 
%let etls_recCheckExist = 0; 
%let etls_change_rows = 0; 
%macro etls_recordCheck; 
   %let etls_recCheckExist = %eval(%sysfunc(exist(work."WVVOJQO"n, DATA)) or 
         %sysfunc(exist(work."WVVOJQO"n, VIEW))); 

   %if (&etls_recCheckExist) %then
   %do;
      %local etls_syntaxcheck; 
      %let etls_syntaxcheck = %sysfunc(getoption(syntaxcheck)); 
      /* Turn off syntaxcheck option to perform following steps  */ 
      options nosyntaxcheck;      data _null_; 
         set work."WVVOJQO"n( obs=1 ); 
         call symput("etls_change_rows",'1'); 
      run; 
      /* Reset syntaxcheck option to previous setting  */ 
      options &etls_syntaxcheck; 
   %end;
%mend etls_recordCheck;
%etls_recordCheck;

%rcSet(&syscc); 

/* new records table - record check  */ 
%let etls_recCheckExist = 0; 
%let etls_new_rows = 0; 
%macro etls_recordCheck; 
   %let etls_recCheckExist = %eval(%sysfunc(exist(work."WVVOL13"n, DATA)) or 
         %sysfunc(exist(work."WVVOL13"n, VIEW))); 

   %if (&etls_recCheckExist) %then
   %do;
      %local etls_syntaxcheck; 
      %let etls_syntaxcheck = %sysfunc(getoption(syntaxcheck)); 
      /* Turn off syntaxcheck option to perform following steps  */ 
      options nosyntaxcheck;      data _null_; 
         set work."WVVOL13"n( obs=1 ); 
         call symput("etls_new_rows",'1'); 
      run; 
      /* Reset syntaxcheck option to previous setting  */ 
      options &etls_syntaxcheck; 
   %end;
%mend etls_recordCheck;
%etls_recordCheck;

%rcSet(&syscc); 

/*---- target table: update/append rows  ----*/ 

/* target table: update change rows  */ 
%if &etls_change_rows ge 1 %then
%do;
   proc sql;
      update netcap."MasterAssetRenewable"n as m
         set "ReCapacity"n = (select "ReCapacity"n from work."WVVOJQO"n as t
                  where m."Id"n = t."Id"n),
             "FeederNo"n = (select "FeederNo"n from work."WVVOJQO"n as t
                  where m."Id"n = t."Id"n),
             "DeclaredGenerationMd"n = (select "DeclaredGenerationMd"n from work."WVVOJQO"n as t
                  where m."Id"n = t."Id"n)
         where exists (select * from work."WVVOJQO"n as t
            where m."Id"n = t."Id"n);   quit;
   %rcSet(&syscc);
%end;

/* target table - append new rows  */ 
%if &etls_new_rows ge 1 %then
%do;
   proc append base = netcap."MasterAssetRenewable"n
               data = work."WVVOL13"n force nowarn;   run;
   %rcSet(&syscc);
%end;

/*---- cross reference table: update/append rows  ----*/ 

/* cross reference table: update change rows  */ 
%if &etls_change_rows ge 1 %then
%do;
   proc sql;
      update work."MasterAssetRenewable_xref"n as m
         set compare_digest = (select compare_digest from work."WVVOJQO"n as t
                  where m."Id"n = t."Id"n)
         where exists (select * from work."WVVOJQO"n as t
            where m."Id"n = t."Id"n);   quit;
   %rcSet(&syscc);
%end;

/* cross reference table - append new rows  */ 
%if &etls_new_rows ge 1 %then
%do;
   proc append base = work."MasterAssetRenewable_xref"n
               data = work."WVVOL13"n( keep = Id compare_digest)   force nowarn;   run;
   %rcSet(&syscc);
%end;

/* Delete work.etls_xref table  */ 
proc datasets lib = work nolist nowarn memtype = (data view);
   delete etls_xref;quit;

%mend etls_scd_type_1;

/* execute etls_scd_type_1  */ 
%etls_scd_type_1



/** Step end SCD Type 1 **/

I have no clue at all now.

Re: Error when loading data into Postgres table from SAS(Invalid input syntax for integer)

LinusH — Fri, 30 Nov 2018 13:48:03 GMT

I don't know what's going on here for your specific situation, but three thing you can check/try:

Set the job/step in debug mode, and run the job in EG. Then you can analyze all work tables prior to updating Postgres.
Check the default maping between SAS formats and Postgres data types
Add OPTIONS MSGLEVEL=I SASTRACE=',,,d' SASTRACELOC=SASLOG NOSTSUFFIX; to your job, then you will semore what's happening between SAS and Postgres.

Re: Error when loading data into Postgres table from SAS(Invalid input syntax for integer)

TomKari — Fri, 30 Nov 2018 16:06:49 GMT

Another suggestion for your decimal problem. Copy your data to a work table, and set the format for all the numeric variables in the work table to BEST15.

That may show up the variable with the fractional part.

Tom

topic Re: Error when loading data into Postgres table from SAS(Invalid input syntax for integer) in SAS Data Management

Error when loading data into Postgres table from SAS(Invalid input syntax for integer)

Re: Error when loading data into Postgres table from SAS(Invalid input syntax for integer)

Re: Error when loading data into Postgres table from SAS(Invalid input syntax for integer)