Are your Have tables already loaded into CAS or are these just normal SAS files and data prep could also happen under Compute? What volumes are you dealing with?
Is your WANT a table or a report? For creating a table something like below could work. I can't test it but I believe if all your tables are in CAS then the whole process will execute within CAS.
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
;
/* restructure your data into a form that's easier to work with */
data writer_role_project;
length writer $40 role $10;
set lead_assistant_project;
keep writer role project;
role='Lead';
do i=0 to countc(Lead_Writer,',');
writer=scan(Lead_Writer,i+1,',');
if not missing(writer) then output;
end;
role='Assistant';
do i=0 to countc(Assistant_Writer,',');
writer=scan(Assistant_Writer,i+1,',');
if not missing(writer) then output;
end;
run;
proc fedsql;
/* create table want as */
select
coalesce(t1.writer,t2.writer) as writer
,sum(case when t1.project='' then 0 else 1 end) as Total_Projects
,sum(case when t1.role='Lead' then 1 else 0 end) as Lead
,sum(case when t1.role='Assistant' then 1 else 0 end) as Assistant
from writer_role_project t1
full join writer t2
on t1.writer=t2.writer
group by coalesce(t1.writer,t2.writer)
;
quit;
... View more
