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;
@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.
Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.
If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website.