I have two tables. Main and pdrp.
If the physicians in the main table are present in the pdrp table then totalscripts should not be displayed(set them to .) else they will have the original number.To achieve this I used the following code. This is not giving the expected result.i.e it is not changing the value of totalscripts to .
Any help is appreciated.
create table crmrk_combine_bsm_final as
when d1.physician_name eq d2.phys_name then totalscripts=. else totalscripts=totalscripts
from main d1 left outer join pdrp d2 on d1.physician_name=d2.phys_name;
I'm getting this warning:
3755 proc sql;
3756 create table final as
3757 select *,
3759 when d1.physician_name eq d2.phys_name then . else totalscripts
3760 end as totalscripts
3761 from main d1 left outer join pdrp d2 on
WARNING: Variable totalscripts already exists on file WORK.FINAL.
That is due to the select *. One of the drawbacks to SQL is there is no drop statement. So you are assigning totalscripts twice. Either spell out all the vars you want, leaving out totalscripts or use a different var name and fix that in another step.
You could also do :
Update table main set totalscripts = . where physician_name in
(select phys_name from pdrp);