topic Re: How to get the distinct values of all columns for a row into another column in SAS Programming

How to get the distinct values of all columns for a row into another column

shari — Fri, 18 Sep 2020 22:04:17 GMT

Hi All,

Below is the example data.

data have;
infile datalines missover;
informat v1 -v5 $2.;
input v1-v5;
cards;
A A B A A
X A Y X Y
S M K H A
X A X
A B B
;
run;

I want to get the result in another column V6:
first row : A/B
Second row: X/A/Y
Third Row : S/M/K/H/A
Fourth Row: X/A
Fifth Row : A/B

Thanks in advance!

Re: How to get the distinct values of all columns for a row into another column

PGStats — Sat, 19 Sep 2020 03:02:36 GMT

Turn the duplicates into missing values (empty strings) and concatenate what's left.

data want;
set have;
array a v1-v5;
do i = 2 to dim(a);
    if whichc(a{i}, of a{*}) < i then call missing(a{i});
    end;
length x $10;
x = catx("/", of a{*});
drop i v:;
run;

Re: How to get the distinct values of all columns for a row into another column

Ksharp — Sat, 19 Sep 2020 12:08:12 GMT

data have;
infile datalines missover;
informat v1 -v5 $2.;
input v1-v5;
cards;
A A B A A
X A Y X Y
S M K H A
X A X
A B B
;
data want;
 set have;
 array x{5} $ 1 ;
 array v{5} $;
 n=0;
 do i=1 to dim(v);
  if v{i} not in x then do;n+1;x{n}=v{i};end;
 end;
 want=catx('/',of x{*});
 drop n i x: ;
run;