CODEWITHSUNDEEP
sqlsasproc-sql
Vladimir Zaguzin
Vladimir Zaguzin

Reputation: 303

sas sql update returns nothing

I have 2 tables - parent and child, with 2 columns in each - id and address, and address looks like this -

\partNumber\a$\sometext....\ - for child

and \partNumber\a$\ - for parent.

And I need to make a table out of it with 2 columns - for every child id I need to get its parent folder. I tried to make it like this by using sql 

update work.Test
set parent_id = ( select pn.DirId 
from work.Parent pn
join work.Child cn on cn.dirPath like pn.dirPath & '%');

just tried another option like this one 

update work.Test
set parent_id = ( select pn.DirId 
                  from work.Parent pn
                  join work.Child cn on 
                  cn.dirPath = substr(pn.dirPath, 1, index( '%', pn.dirPath) +1));

but still the same result

And even it gives me 0 error and shows in a log that it did updates on all records, as a final result I get nothing on my table.

Upvotes: 1

Views: 63

Answers (2)

Tom
Tom

Reputation: 51566

You can probably just use the EQT comparison to find the addresses that have similar prefixes.

data child ;
  input child_id address $50. ;
cards;
1 \partNumber\a$\sometext....\
2 no_parent
;
data parent ;
  input parent_id address $50.;
cards;
501 \partNumber\a$\
;

proc sql ;
  create table want as 
    select a.child_id,b.parent_id,a.address,b.address as parent_address
    from child a 
    left join parent b
    on a.address eqt b.address
  ;
quit;

Results:

                   parent_
Obs    child_id       id      address                         parent_address

 1         1         501      \partNumber\a$\sometext....\    \partNumber\a$\
 2         2           .      no_parent

Upvotes: 2

Petr
Petr

Reputation: 376

I'm not sure if I can help not knowing where the table Test comes from.

Anyway, cn.dirPath like pn.dirPath & '%' is very likely not doing what you want. Try cn.dirPath like trim(pn.dirPath) || '%'

Edit: I added trim(), pn.dirPath is likely to have trailing blanks.

Upvotes: 1

Related Questions