cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
RichM
‎12-11-2017
  • 5 Posts
  • 0 Likes Given
  • 0 Solutions
  • 0 Likes Received

Re: Proc Append failing due to IDENTITY_INSERT error for table column ...

by Calcite | Level 5 in SAS Programming
‎10-12-2017 02:13 PM
‎10-12-2017 02:13 PM
Hi,   SecurityMasterID is definitely a unique key.  The strange thing is that this proc sql works.   proc sql noprint;     INSERT INTO warehse.cmmn_data_secmaster (identifier,exchange_country,variable_id,value) VALUES ('rm2','rm2','rm2','rm2'); quit;   I believe that this tells me that the issue is not on the database since the insert correctly results in an auto-incremented value for SecurityMasterID in the database when it is not in the insert.  But I may be wrong.   I also know for sure that the dataset WORK.securitymaster_trans does not have a SecurityMasterID column.  Maybe I need to sort out how to see the IDENTITY_INSERT setting value prior to running the proc append.  The error says it is off but maybe it isn't.  Probably, grasping at straws with that thought.   Thanks, Rich       ... View more

Proc Append failing due to IDENTITY_INSERT error for table column that...

by Calcite | Level 5 in SAS Programming
‎10-12-2017 11:52 AM
‎10-12-2017 11:52 AM
Hi,   This proc append proc append base = warehse.cmmn_data_secmaster data = WORK.securitymaster_trans force;    run;   Is generating this error   WARNING: Variable SecurityMasterID was not found on DATA file. WARNING: Variable exchange_country has different lengths on BASE and DATA files (BASE 3 DATA 6). WARNING: Variable variable_id has different lengths on BASE and DATA files (BASE 30 DATA 13). WARNING: Variable value has different lengths on BASE and DATA files (BASE 256 DATA 100). ERROR: CLI execute error: [OpenLink][ODBC][SQL Server]Cannot insert explicit value for identity column in table         'cmmn_data_secmaster' when IDENTITY_INSERT is set to OFF. (544) : [OpenLink][ODBC][SQL Server]General SQL Server error:         Check messages from the SQL Server (544) : [OpenLink][ODBC][SQL Server]Attempt to bind to a non-existent column (544)   WORK.securitymaster_trans has these column identifier exchange_country variable_id value   The table warehse.cmmn_data_secmaster has the same columns but it also has SecurityMasterID which is an auto-incremented integer primary key. There are no additional columns.   Finally, my question Why is proc append failing due to SecurityMasterID missing in the data set?  Obviously, we would want to the have the database create the PK integer.   Thanks in advance for you help! ... View more

Populating data set from Filename dirlist pipe "find tmpFolder"

by Calcite | Level 5 in SAS Programming
‎06-21-2017 07:29 PM
‎06-21-2017 07:29 PM
I'm running in linux and I'm trying to parse a number of rows formatted as follows (file name and directory structure will be variable) 06/16/17 09:40:19.8344320410 /workspace/alphabuilder/data-jp/D160125.DB   The goal from this example is a data set where each record includes Date = 06/16/17 Time =09:40:19.83  File = D160125.DB Folder = /workspace/alphabuilder/data-jp/   My Sas code   /*Test varaibles*/ %let folder = /workspace/uat/card2/ai/x796785/V_drive/alphabuilder/data-jp/ ; %let prefix = M ; %let suffix = db ;   Filename dirlist pipe "find &folder. -iname &prefix.*&suffix. -printf '%TD %TT %p\n'";   Data &outlist (keep=folder filename date time);                                                Infile dirlist end=last;        format date mmddyy10. time time8.;        input;                *Create the folder= file directory location;        date = input(_infile_,mmddyy10.);   time = input(substr(_infile_,10,18),time8.);   filename = substr(_infile_,length(_infile_) - index(reverse(_infile_),"/") + 2,length(_infile_));   folder = substr(_infile_,29,length(_infile_)- index(reverse(_infile_),"/") );     Run;   My problem: Folder includes the filename at the end /workspace/alphabuilder/data-jp/D160125.DB   I tried a number of variations including folder = substr(_infile_,29,length(_infile_)- 5 ); folder = substr(_infile_,29,35 );   I'm sure this is a rookie issue but I haven't been able to google a solution. ... View more

Re: ERROR when using restricted SQL keyword (SQL Server 13.0.4422.0)

by Calcite | Level 5 in SAS Programming
‎06-20-2017 05:39 PM
‎06-20-2017 05:39 PM
Please note adding the modifying the sql didn;t work.  I was getting the same error   Maybe try   select distinct constraint as CONS or   select distinct 'constraint'n as CONS   The explicit passhtrough worked but I had to pull back the whole table (luckily, not a big table) proc sql;  connect using warehse;  select * from connection to warehse (select distinct constraint as CONS..);       ... View more

ERROR when using restricted SQL keyword (SQL Server 13.0.4422.0)

by Calcite | Level 5 in SAS Programming
‎06-19-2017 04:28 PM
‎06-19-2017 04:28 PM
  I'm getting the following error when I run this SAS code.  The backend database is SQL Server (13.0.4422.0) connecting via ODBC.  They used a restricted keyword as a column name, which is causing the error.  I'm not sure the implications of changing the column name.  Is there a way to modify my sas so I can keep the keyword column name.  BTW: This runs under 9.2.  I'm porting the code to 9.4. Also, I tried enclosing constraint with backets [constraint]   proc sql noprint;                                select distinct constraint into :PBD_binary_constraints separated by " " from warehse.cmmn_opt_assetbounds                                                 where event_multiplier = 0 or event_multiplier = .; quit;   ERROR: CLI describe error: [DataDirect][ODBC SQL Server Wire Protocol driver][Microsoft SQL Server]Incorrect syntax near the         keyword 'constraint'.     ... View more
Contact Me
Online Status
Offline
Date Last Visited
‎12-11-2017 03:09 PM
Quick links