sas sql update returns nothing

Question

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.

Why are you using UPDATE to MAKE a table? Shouldn't that be CREATE TABLE instead? — Tom, Sep 19 '17 at 14:58
sorry, i made this table before, loaded child IDs to it and now i'm trying to add this parent IDs — Vladimir Zaguzin, Sep 19 '17 at 15:23

score 2 · Accepted Answer · answered Sep 19 '17 at 15:29

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

that's amazing. it works. and thanks for showing me this eqt function — Vladimir Zaguzin, Sep 19 '17 at 15:37

Petr · Answer 2 · 2017-09-19T14:58:24.740

1

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.

edited Sep 19 '17 at 14:58

answered Sep 19 '17 at 14:50

Petr

376
1
6

Test as created before and I load all child IDs into it before I starded updating it with parent IDs. And again even it has no errors and shows like all lines was updated There still no updates on Test tables its self – Vladimir Zaguzin Sep 19 '17 at 15:29
Create table is faster than create + update. Also, in the original query, the nested select doesn't depend on the data in the test table and possibly returns more than one value. Tom's solution using "eqt" is better than "like" IMO. – Petr Sep 19 '17 at 15:56

sas sql update returns nothing

2 Answers2