Help using Base SAS procedures

SET KEY RETRIEVAL: error

Reply
N/A
Posts: 0

SET KEY RETRIEVAL: error

Hi all,

I am getting the error below;

Index MANDT requested but not found on data set SRC.DEV.

When submitting the following code. It's confusing as I am not calling a table called DEV in the src library? I have a set key= /unique on the line in bold but on the table src.device_man.

Any ideas? Thanks in advance
Lewis


data
d0188_flow_man (
keep=
file_identifier
dataflow_id
file_creation_timestamp
key_meter_supplier_id
key_charging_machine_number
terminal_transaction_number
customer_payment_date
customer_payment_amount
meter_id
standing_charge
debt_recovery_rate
active_credit_on_key
total_credit_accepted
credit_balance
mpan_core
reading_date_and_time
register_reading1
register_reading2
prepayment_unit_rate1
prepayment_unit_rate2
)
d0188_file_man (
keep=
file_identifier
dataflow
from_market_participant_role
from_market_participant_id
to_market_participant_role
to_market_participant_id
file_creation_timestamp
sending_application_id
receiving_application_id
broadcast
test_data_flag
file_identifier_t
total_group_count
checksum
flow_count
file_completion_timestamp
)
;
infile dir truncover;
input filename $255.;
filepath = "&d0188_dir\" || filename;

infile d0188 filevar=filepath end=done dsd dlm='|';
bag_it = 0;

do while (^done);
input group :$char3. @;

select (group);
when ('ZHV') /* header */
do;
input
file_identifier :$char10.
dataflow :$char8.
from_market_participant_role :$char1.
from_market_participant_id :$char4.
to_market_participant_role :$char1.
to_market_participant_id :$char4.
file_creation_timestamp :$char14.
sending_application_id :$char5.
receiving_application_id :$char5.
broadcast :$char1.
test_data_flag :$char4.
;
dataflow_id = 0;
bag_it = 0;
end;
when ('386')
do;
if bag_it then
output d0188_flow_man;

input
key_meter_supplier_id :$char20.
key_charging_machine_number :7.
terminal_transaction_number :6.
customer_payment_date :$char8.
customer_payment_amount :9.2
meter_id :$char10. @
;

set src.device_man (rename=(serge=meter_id)) key=meter_id /unique; bag_it = (_iorc_ = %sysrc(_sok));

if bag_it then
do;
input
standing_charge :6.2
debt_recovery_rate :6.2
active_credit_on_key :6.2
key_debt :8.2
key_date_stamp :$char14.
reading_date_and_time :$char14.
total_credit_accepted :8.2
credit_balance :7.2
mpan_core :$char13.
;
register_reading1 = .;
register_reading2 = .;
prepayment_unit_rate1 = .;
prepayment_unit_rate2 = .;
end;
else
do;
_error_ = 0;
input;
end;

dataflow_id = dataflow_id + 1;
end;
when ('387')
if bag_it then
do;
input meter_register_id :$char2. @;

select (meter_register_id);
when ('0', '00', '1', '01', 'R1', 'A1', 'A8')
input register_reading1 :10.1 prepayment_unit_rate1 :6.2;
when ('2', '02', 'R2', 'A7')
input register_reading2 :10.1 prepayment_unit_rate2 :6.2;
otherwise
input @'|' @'|'; /* skip any additional registers */
end;
end;
else
input;
when ('ZPT') /* trailer */
do;
if bag_it then
output d0188_flow_man;

input
file_identifier_t :$char10.
total_group_count :10.
checksum :10.
flow_count :8.
file_completion_timestamp :$char14.
;
output d0188_file_man;
end;
end;
end;
run;
Super User
Posts: 9,681

Re: SET KEY RETRIEVAL: error

Yes.
Before useing set key=MANDT /unique ,You need to make an index for him.
Try to use proc datasets OR proc sql to make an index.



Ksharp
N/A
Posts: 0

Re: SET KEY RETRIEVAL: error

Ksharp,

Thanks for trying to help.

However I am not using key=MANDT /unique .

I am using set src.device_man (rename=(serge=meter_id)) key=meter_id /unique;

I have checked the src.device_man tables and there is already an index on serge (which I then rename to meter_id).

I am confused as the error is on MANDT for the table src.dev?? This table doesn't exist.

I have found this in the help file;

SETKEY works only after SET is called in the SCL program or when a list identifier is passed. The list identifier must point to a list that contains the values of the index key columns. Once an active key is set through SETKEY, it remains active until

the table is closed

another key is set

the current setting is cleared by passing the table identifier alone to SETKEY.


However i am unsure how to clear the setkey?

Thanks,
Lewis
N/A
Posts: 0

Re: SET KEY RETRIEVAL: error

Thanks for you're help.

I removed the index and reapplied using proc datasets and it now runs fine.

Many thanks,
Lewis
Ask a Question
Discussion stats
  • 3 replies
  • 155 views
  • 0 likes
  • 2 in conversation