DATA Step, Macro, Functions and more

Proc sql

Reply
Super Contributor
Posts: 673

Proc sql

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.
proc sql;
create table crmrk_combine_bsm_final as
select *,
case
when d1.physician_name eq d2.phys_name then totalscripts=. else totalscripts=totalscripts
end
from main d1 left outer join pdrp d2 on d1.physician_name=d2.phys_name;
quit;
Super Contributor
Posts: 359

Re: Proc sql

case
when d1.physician_name eq d2.phys_name then =.
else totalscripts
end as totalscripts

You wrote it in datastep speak.
Super Contributor
Posts: 673

Re: Proc sql

Filp,
I'm getting this warning:
3755 proc sql;
3756 create table final as
3757 select *,
3758 case
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
3761! d1.physician_name=d2.phys_name;
WARNING: Variable totalscripts already exists on file WORK.FINAL.
Super Contributor
Posts: 359

Re: Proc sql

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);
Ask a Question
Discussion stats
  • 3 replies
  • 161 views
  • 0 likes
  • 2 in conversation