Proc SQL problem:Unresolved reference to table/correlation name

[irinnew]

I have two datasets: inputdata and ref

I need NO field’s values in inputdata be updated with sub_num field’s values.

The condition is that first 9 characters from NO field’s value should be equal first 9 characters from mc_sub_num field’s values



This is the Error which Log generated:


proc sql;
347 update inputdata k
348 set NO = (select l.sub_num from ref l
349 where substr(k.NO,1,9)=substr(l.mc_sub_num,1,9)
350 )
351 where l.sub_num in(select l.sub_num from ref l);
ERROR: Unresolved reference to table/correlation name l.
352 quit;

What I am doing wrong?

Thank you!

Irin

 

[klaz2002]

Where have you told the SAS SQL proc what library 'L' stands for?
Check that you have assigned the L library ref and that the table sub_num exists.
Klaz

 

[irinnew]

Klaz,

I am confused as my understanding is that L is not a library: this is ref table aliase.

I mean... k is an aliase of inputdata table while l is ref table aliase...

Didn't I specified ref aliase in the following statement?

select l.sub_num from ref L

How should I spesify this aliase instead?

Thank You in advance1

 

[klaz2002]

Irinnew,
You are right. L is the alias to the table 'ref'. Check that the table ref exists. I would also modify the where statement so that there is no recursion. (You point to itself) Modify the where statement so that your proc knows which table to look at, but not more than that. See if this still gives you the old error. If not than try to add more logic to the where statement and see when it fails. But keep in mind that SAS has to load the table based on what you put in the where statement. If you point to itself to tell it what to load this may cause the error.
Klaz

 

[irinnew]

I am not sure how to make the WHERE clause just give PROC an idear what table to look at. I mean...to make it shorter?

where l.sub_num in(select l.sub_num from ref l)

Therefore I am trying to take off the second WHERE clause at all.

However it complains that Subquery evaluated to more than one row.

Is there any alternative way of doing the same thing?
In other words to replace INPUTDATA.NO with ref.subnum in order substr(inputdata.NO,1,9)=substr(ref.mc_sub_num,1,9)?

Thank You!

Irin

 

[ChrisW75]

The problem is here

351    where l.sub_num in(select l.sub_num from ref l);

You are referring to l in the outer query when it is only defined in the inner query. I think this line should actually be

where k.sub_num in(select sub_num from ref)

 

[irinnew]

Chris,

But the problem is that sub_num (as well as mc_sub_num) is not a part of k (inputdata table). It is a part of l(ref table):-(

 

[ChrisW75]

The last where clause is really not doing anything.

where l.sub_num in(select l.sub_num from ref l);

it's basically saying "Bring back every record where the value of sub_num in REF is equal to the value of sub_num in REF". You should just be able to get rid of this second sub query. It sounds like you've done this and got a new error message that the sub-query evaluates to more than one row. This probably means that you have duplicate records in REF.
Also I would recommend breaking this up a bit. Currently you are changing the value of NO if the first part of NO matches to the first part of mc_sub_num in REF. I would create a NEW column in INPUTDATA which you would update accordingly, then overwrite NO in a separate step, this will allow you to check it over and make sure the update is working correctly (as otherwise you'll find it rather difficult to see what records were updated).

 

[irinnew]

I have beaten my head trying to resolve the problem and
It looks like I did not formulete it properly....

I have a source table WHICH HAS MULTIPLE RECORDS for each PLICYNO.
I also have a small REF table. They have a common field POLICYNO

I need to update a part of Policyno in the SOURCE table substr(policyno1,9) with a part of MC_SUB_NUM from REF table substr(policyno1,9) .

This is an urgent issue...Could you give me a hand with SQL query?

Thank you!!
------------------------------------------------

Table SOURCE

Policyno date
111111111-mara 10/12/2000
111111111-mara 12/12/2001
222222222-ira 11/20/2002
222222222-ira 10/8/2003
333333333-yana 12/3/2003
333333333-yana 9/12/2000
333333333-yana 8/3/2003


Table: REF

mc_sub_num POLICYNO
aaaaaaaaa-mara 1111111110000
bbbbbbbbb-ira 2222222220000
cccccccccc-yana 3333333330000

The output needed

Policyno date
aaaaaaaaa -mara 10/12/2000
aaaaaaaaa -mara 12/12/2001
bbbbbbbbb -ira 11/20/2002
bbbbbbbbb -ira 10/8/2003
cccccccccc -yana 12/3/2003
cccccccccc -yana 9/12/2000
cccccccccc -yana 8/3/2003

 

[irinnew]

ooops !
Please disregard my previous message. Sorry about it. The one below is correct...

I have a INPUTDATA table WHICH HAS MULTIPLE RECORDS for the same PLICYNO.
I also have a small NEW table. They have a common field POLICYNO

I need to update a part of Policyno in the INPUTDATA table substr(policyno1,9) with a part of MC_SUB_NUM from REF table substr(policyno1,9) .

I HAVE BEATEN MY HEAD :-( Could you give me a hand with SQL query?

Thank you!

Table INPUTDATA

Policyno date
111111111-mara 10/12/2000
111111111-mara 12/12/2001
222222222-ira 11/20/2002
222222222-ira 10/8/2003
333333333-yana 12/3/2003
333333333-yana 9/12/2000
333333333-yana 8/3/2003


Table: NEW

mc_sub_num POLICYNO
aaaaaaaaa000a 111111111-mara
bbbbbbbbb000 222222222-ira
cccccccccc000 333333333-yana

The output needed

Policyno date
aaaaaaaaa -mara 10/12/2000
aaaaaaaaa -mara 12/12/2001
bbbbbbbbb -ira 11/20/2002
bbbbbbbbb -ira 10/8/2003
cccccccccc -yana 12/3/2003
cccccccccc -yana 9/12/2000
cccccccccc -yana 8/3/2003

 

[klaz2002]

Irinew,
Must you use Proc SQL? You can do this in several data steps.

data source;
  set your_source;
  id_field = input(substr(policyno,1,9),8.);
run;
data ref;
  set your_ref;
  id_field = input(substr(policyno,1,9),8.);
run;
proc sort
  data = source
  out  = source_sorted;
  by id_field;
run;
proc sort
  data = ref
  out  = ref_sorted;
  by id_field;
run;
data combined;
  merge source_sorted (in=in_src rename=(policyno=policynoold))
        ref_sorted (in=in_refrename=(policyno=policynoold2));
  by id_field;
  rename mc_sub_num = policyno;
run;

If you insist on the Proc SQL way why not try the following.

proc sql;
update source as S
  set policyno = (select mc_sub_num from ref as R
                  where substr(R.policyno,1,9) = 
                        substr(S.policyno,1,9));
  quit;

I hope that this helps you. I would suggest that if its a report your interested in creating, do not use the update feature, but instead use a new variable and display that on a report.
Klaz

 

[irinnew]

Klaz,

Thank you for your response. It looks like the code does substitutes part of policyno. However something looks really strange….

I have 8586 records in the SOURCE table and I have 5205 records in REF table. Final result (COMBINED table) contains 9534 records while I would expect 8586 records exactly as in was in the source. I have a feeling it was caused by multiple records in Source or REF table (or in Both?).

How can I change the code in order to avoid this unexpected increase?

Thank you!

Irin

 

[irinnew]

One more thing...

LOG GAVE ME A NOTE:

NOTE: Invalid argument to function INPUT at line 426 column 14

I assume it because I have a few members whose policyno starts with a letter rather than with figure (like N11111111)

What can be done?

Thank you!

Irin

 

[klaz2002]

Irinew,
I made a slight error in the code (substr() function should start @ 2 and go for 9 chars).
I also added a where clause so that the extra records should not port over. I believe that this also will stop bad updates from occuring as it doesn't update and blank out the source's policyno if it finds a blank.

proc sql;
update source as S
  set policyno = (select mc_sub_num from ref as R
                  where substr(R.policyno,2,9) = 
                        substr(S.policyno,1,9))
  where (select mc_sub_num from ref as R
                  where substr(R.policyno,2,9) = 
                        substr(S.policyno,1,9)) ne '';

  quit;

I hope this finaly helps you.
Klaz

 

[irinnew]

Klaz,
I actually told about SAS version rather than SQL... I will try SQL now

Thank you,

Irin

 

[irinnew]

Klaz,

I have just tried it and log said:

531 PROC SQL;
532 update chfm.jinputdata as S
533 SET POLICYNO=(SELECT MC_SUB_NUM from chfm.jnew as R
534 WHERE substr(R.policyno,2,9)=
535 substr(S.policyno,1,9))
536 where (select mc_sub_num from chfm.jnew as R
537 WHERE SUBSTR(r.POLICYNO,2,9)=
538 SUBSTR(S.policyno,1,9)) ne '';
NOTE: No rows were updated in CHFM.JINPUTDATA.

539 quit;

Also I am not sure why substr() should start from position 2 ? I do not have blank policyno in the file...

When I tried 1,9 i got an error
ERROR: Subquery evaluated to more than one row.

p.s. In response to your earlier question-I do not issist upon SQL code. Sas syntax would be fine and it updates but for some reason generates multiple records....

 

[klaz2002]

If you follow the SAS way (not SQL) all you have to use is the 'in' dataset option correctly and that should suppress the multiple records insert.
Klaz