Hi @ak2011
You can try this. Hope this help!
The only thing to adapt is the decode of idchem variable at the beginning of the program in order to create the corresponding character variable with the flag name.
Best,
data idnew1;
input id$ job idchem;
id_job=catx('_', id, job);
length idchem_char $7;
if idchem = 990005 then idchem_char = 'cla_exp';
else if idchem = 990021 then idchem_char = 'bio_exp';
else if idchem = 210701 then idchem_char = 'amo_exp';
else if idchem = 211700 then idchem_char = 'chl_exp'; /* to adapt */
datalines;
os1 1 990005
os1 1 990021
os1 1 211700
os1 2 211700
os1 2 990021
os1 2 210701
os1 2 990005
os2 1 210701
os2 1 990005
os2 2 990021
os2 3 210701
os2 3 990005
os3 3 210701
os3 1 211700
os4 1 210701
os4 1 990005
os4 1 211700
;
run;
/* 1. Generate macrovariables */
proc sql noprint;
/* Retrieve the list of distinct idchems (num) in a macrovariable : &list_idchem */
select distinct idchem into:list_idchem separated by " " from idnew1;
/* Retrieve the list of distinct idchems (format) in a macrovariable : &list_idchem_char */
select distinct idchem_char into:list_idchem_char separated by " " from idnew1;
/* Retrieve the number of distinct idchems in a macrovariable : &count_idchem */
select count(distinct idchem_char) into:count_idchem trimmed from idnew1;
quit;
data _null_;
length temp $2000;
temp=cat('"',tranwrd("&list_idchem_char"," ",'" "'),'"');
call symput('list_idchem_char_q',temp);
run;
%put &list_idchem;
%put &list_idchem_char;
%put &list_idchem_char_q;
%put &count_idchem;
/* 2. Transpose data and concatenate idchem */
proc sort data=idnew1;
by id_job;
run;
data idnew_tr;
set idnew1;
array flag(4) $ _temporary_;
by id_job;
retain flag;
if first.id_job then do;
count=0;
call missing(of flag(*));
end;
count+1;
do i=1 to dim(flag);
flag(count)=idchem_char;
end;
idchem_list = catx(" ", of flag(*));
if last.id_job then output;
drop count i idchem idchem_char id job;
run;
/* 3. Create flag variables */
data idnew_flag;
set idnew_tr;
array list(&count_idchem) $ (&list_idchem_char_q);
array flag(*) &list_idchem_char;
do i=1 to dim(list);
if indexw(idchem_list, list(i)) > 0 then flag(i) = 1;
else flag(i)=0;
end;
drop i idchem_list list1-list&count_idchem;
run;
proc print data=idnew_flag;
run;
/* 3. Generate as many 2x2 tables as combinations */
%macro combi (dataset);
proc sql;
select count(*) into :nbvar from dictionary.columns where libname='WORK' and memname='IDNEW_FLAG' ;
select name into :mv1- :mv%cmpres(&nbvar) from dictionary.columns where libname='WORK' and memname='IDNEW_FLAG' ;
quit;
%do i=2 %to %eval(&nbvar) ;
%do j=%eval(&i+1) %to %eval(&nbvar);
proc freq data=idnew_flag;
tables &&mv&i*&&mv&j ;
run;
%end;
%end;
%mend;
%combi ();
... View more
