Renaming variable based on a correlation file and Subquerry error

[Blandine]

Hello, I am having a file  with data fragmentation. The variable "Structure_name" (in the file WA.WA_datanewname) has 2 ID for the same bridge and I'm trying to make it 1 ID using a correlation file (fh_wa02) that was given to me. The correlation file has old_structure_number_008 and New_structure_number_008, so basically replace I want to replace the old_structure_number_008 with the New_structure_number_008 without lossing any data. But I am having 2 problems:

1-  The subquery error below. I solve it by deleting that structure number which is probably not an efficient solution

ERROR: Subquery evaluated to more than one row.
NOTE: Correlation values are: Structure_Number='WN-005051656925

2. It's seemed like I am losing some bridge ID. I have 4 bridges that I checked if the code correctly replace the  old_structure_number_008 with the New_structure_number_008 but the code simply deleted the old_structure_number_008 . I don't know what to do

Here is my code

proc sql;
create table WA.WA_NBI_data_corrected as
select
case when exists (select * from fh.fhwa_WA02 where OLD_STRUCTURE_NUMBER_008=Structure_Number)
then (select NEW_STRUCTURE_NUMBER_008 from fh.fhwa_WA02 where OLD_STRUCTURE_NUMBER_008=Structure_Number)
else Structure_Number
end as Structure_Number
, Year_Built
, ADT
, STRUCTURE_KIND_043A
, STRUCTURE_KIND_043B
, Bridge_Age
, DECK_COND_058
, SUPERSTRUCTURE_COND_059
, SUBSTRUCTURE_COND_060
, Deck_Structure_Type
, Inspection_Year
from WA.WA_datanewname;
quit;

[jimbarbour]

@Blandine,

I see that for some reason no one has replied.  Is this still an active question?  If it is still active, I'll help you if I can.

---

@Blandine wrote:

1-  The subquery error below. I solve it by deleting that structure number which is probably not an efficient solution

ERROR: Subquery evaluated to more than one row.
NOTE: Correlation values are: Structure_Number='WN-005051656925

case when exists (select * from fh.fhwa_WA02 where OLD_STRUCTURE_NUMBER_008=Structure_Number)

---

Have you tried switching the subquery from a simple Select to a Select Distinct?  That should eliminate the "Subquery evaluated to more than one row" problem.

Jim

[Patrick]

@Blandine Yes, if this is still an active problem let us know. Please also provide some representative sample data via SAS data steps that shows the issue and the desired outcome.

Not sure that I really understand what you describe as it sounds like you would end-up in duplicate ID's with what you're trying to achieve. Providing representative sample data will make it much easier for us to understand the problem and provide code that's actually fully working.