CODEWITHSUNDEEP
sas
fred wu
fred wu

Reputation: 55

how to update a table when the where clause's value has more than 32 characters in module of proc SQL of SAS enterprise guide

here is the scenario: at the beginning, I prepare to import a csv file; then in Proc SQL, I insert the record of temp data set into database, the following are my difficulties:

for the sake of audit, I want to update one record in a table in the database to record this insert operation:

update table1 
set inserted_record=&SQLOBS, insert_date=today() 
where filename=&csv_file_name;

But the length of the filename is more than 32 character.what should I do ? Thanks!

My SAS code is like the following:

DATA Temp1;

File_name="kkkkkkkkkkk_product_information_20200101_20211005_FULL.csv" run;

Data work.temptable; length Product_ID $36 Worth_USD $9; Format Product_ID Char36. Worth_USD Char9.; Informat Infile input Run;

Libname lib1 Teradata user=userid Password=xxxxxx

proc SQL; insert into lib1.table1(col1,col2) select prodcut_id,worth_usd from work.temp_table; update lib1.import_summary set inserted_record=&sqlobs,operated_date=today() where file_name='&file_name'; Run;

according to the log, the SAS code can do the insert operation successfully while the update operation is not (the log shows "No rows were updated"). I check the table of import_summary, there is already a record whose file_name is "kkkkkkkkkkk_product_information_20200101_20211005_FULL.csv". It should be updated. Who can provide the comments? Thanks!

Upvotes: 0

Views: 215

Answers (1)

Reeza
Reeza

Reputation: 21274

From your code shown this shouldn't affect anything, you do need to have quotes around the file name as it's likely a character field but the 32 char limit is only on data set names which this is not and the file name doesn't have a 32 character limit.

update table1 
set inserted_record=&SQLOBS, insert_date=today() 
where filename="&csv_file_name";

EDIT: This needs double quotes, not single quotes:

 where file_name='&file_name';

Upvotes: 1

Related Questions