The code below uses the ability to sort the SYSTEM values (call sortc or call sortn), and then counts contiguous identical values. If, as you said, the SYSTEM values are character, then:
data have;
input Id @3 (system1 system2 system3 system4) ($4. +1);
datalines;
1 0001 0001 0001 0002
2 0002 0001 0002 0003
3 0003 0002 0002 0004
run;
data want (drop=_:);
set have;
call sortc(of system:);
length _str $300; /*Long enough to hold all the SYSTEM values */
_str=catx(' ',of system:);
mode_val=' ';
mode_frq=0;
do while (_str^=' ');
do _frq=1 by 1 until(scan(_str,_frq+1)^=scan(_str,1));
end;
if _frq>mode_frq then do;
mode_val=scan(_str,1);
mode_frq=_frq;
end;
_str=tranwrd(_str||' ',scan(_str,1)||' ','');
end;
set have; /*Reread the same obs, so SYSTEM values are in original order*/
run;
And if the SYSTEM variables are numeric, you only have to change three lines of code (indicated by /*!!For numeric vals!!*/ below):
data want (drop=_:);
set have;
call sortn(of system:); /*!!For numeric vals!!*/
length _str $300; /*Long enough to hold all the SYSTEM values */
_str=catx(' ',of system:);
mode_val=.; /*!!For numeric vals!!*/
mode_frq=0;
do while (_str^=' ');
do _frq=1 by 1 until(scan(_str,_frq+1)^=scan(_str,1));
end;
if _frq>mode_frq then do;
mode_val=input(scan(_str,1),best32.); /*!!For numeric vals!!*/
mode_frq=_frq;
end;
_str=tranwrd(_str||' ',scan(_str,1)||' ','');
end;
set have; /*Reread the same obs, so the SYSTEM values are in original order*/
run;
And note that, while the CALL SORTC (or CALL SORTN) destroys the original order of values for SYSTEM1 through SYSTEM4, at the end of the DATA step is a second SET HAVE, which rereads the same observation, thereby restoring SYSTEM values to original order. This uses almost no resources, since the reread is retrieving data from cached memory, not the disk storage device.
... View more
