data writer;
infile datalines truncover dsd dlm='|';
input writer:$40.;
datalines;
Joe
Mac
Maggie
Sam
;
data lead_assistant_project;
infile datalines truncover dsd dlm='|';
input Lead_Writer:$40. Assistant_Writer:$40. Project:$40.;
datalines;
Joe|Mac,Maggie|Earwax for Dummies
Joe||Clock Repair
Sam|Joe|Desk Fans: A Celebration
;
data have;
set lead_assistant_project(rename=(Assistant_Writer=_Assistant_Writer));
length Assistant_Writer $ 80;
if not missing(_Assistant_Writer) then do;
do i=1 to countw(_Assistant_Writer,',');
id+1;
Assistant_Writer=scan(_Assistant_Writer,i,',');
output;
end;
end;
else do;id+1;output;end;
drop _Assistant_Writer i;
run;
proc transpose data=have out=temp;
by id Project;
var Lead_Writer Assistant_Writer ;
run;
proc sql;
create table want as
select a.writer,coalesce(Total_Projects,0) as Total_Projects,
coalesce(Lead,0) as Lead,coalesce(Assistant,0) as Assistant
from writer as a left join (
select col1,count(distinct Project) as Total_Projects,
(select count(distinct Project) from temp where col1=a.col1 and _NAME_='Lead_Writer') as Lead,
(select count(distinct Project) from temp where col1=a.col1 and _NAME_='Assistant_Writer') as Assistant
from temp as a
where col1 is not missing
group by col1
) as b on a.writer=b.col1;
quit;
... View more
