topic Re: Proc sql in SAS Programming

Proc sql

SASPhile — Fri, 14 Aug 2009 18:46:09 GMT

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;

Re: Proc sql

Flip — Fri, 14 Aug 2009 18:51:48 GMT

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

You wrote it in datastep speak.

Re: Proc sql

SASPhile — Fri, 14 Aug 2009 18:59:42 GMT

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.

Re: Proc sql

Flip — Fri, 14 Aug 2009 19:12:41 GMT

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);