BookmarkSubscribeRSS Feed
SASPhile
Quartz | Level 8
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;
3 REPLIES 3
Flip
Fluorite | Level 6
case
when d1.physician_name eq d2.phys_name then =.
else totalscripts
end as totalscripts

You wrote it in datastep speak.
SASPhile
Quartz | Level 8
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.
Flip
Fluorite | Level 6
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);

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 3 replies
  • 660 views
  • 0 likes
  • 2 in conversation