Hi,
I need to keep only unique values for a row on multiple column.
Here is an example:
Input:
Name, Language1,Language2,Language1
Tom, EN,EN,IT
MARK, ES,ES,EN
Desired Output:
Name, Language1,Language2,Language1
Tom, EN,IT,''
MARK, ES,EN,''
Thank you in advance for your help.
Fabio
Fabio,
I am assuming that you meant to type Language1, Language2 and Language3. You can drop the input names in the datastep and then rename the lang1 - lang3 variables to be the original Language names.
proc sql; drop table work.have; quit;
data have;
format name $16. Language1 $2. Language2 $2. Language3 $2.;
input name Language1 Language2 Language3;
datalines;
Tom EN EN IT
MARK ES ES EN
;
run;
proc sql; drop table work.want; quit;
data want;
set have;
array lanz{3} $ language1 language2 language3;
array lano{3} $2 lang1 lang2 lang3 (3*'');
do k=1 to 3;
lano(k) = ' ';
do j=1 to 3;
if j> 1 then do;
if lanz(k) not = ' ' and lanz(k) not = lano(j-1) and lano(j) = ' ' then lano(j) = lanz(k);
end;
else do;
if lanz(k) not = ' ' and lano(j) = ' ' then lano(j) = lanz(k);
end;
end;
end;
run;
proc print data=want;
run;
Message was edited by: James Willis If the language(n) needs to be checked in all lang(n) fields, then the do statement will need to be built differently.
As in most analysis situations, it's better to have data values in rows rather than in columns.
By transposing your data, you can then use standard procedures, such as SQL, SORT:
select distinct name, language
from want;
You could also use arrays on such data. The below would give you a space delimited list of distinct languages.
data have;
attrib Name L1 L2 L3 format=$200.;
name="Tom"; l1="EN";l2="EN"; l3="IT"; output;
name="MARK"; l1="ES"; l2="ES"; l3="EN"; output;
run;
data want (drop=i);
set have;
attrib result format=$2000.;
array l{3};
do i=1 to 3;
if index(result,l{i})=0 then result=strip(result)||" "||strip(l{i});
end;
run;
Fabio,
I am assuming that you meant to type Language1, Language2 and Language3. You can drop the input names in the datastep and then rename the lang1 - lang3 variables to be the original Language names.
proc sql; drop table work.have; quit;
data have;
format name $16. Language1 $2. Language2 $2. Language3 $2.;
input name Language1 Language2 Language3;
datalines;
Tom EN EN IT
MARK ES ES EN
;
run;
proc sql; drop table work.want; quit;
data want;
set have;
array lanz{3} $ language1 language2 language3;
array lano{3} $2 lang1 lang2 lang3 (3*'');
do k=1 to 3;
lano(k) = ' ';
do j=1 to 3;
if j> 1 then do;
if lanz(k) not = ' ' and lanz(k) not = lano(j-1) and lano(j) = ' ' then lano(j) = lanz(k);
end;
else do;
if lanz(k) not = ' ' and lano(j) = ' ' then lano(j) = lanz(k);
end;
end;
end;
run;
proc print data=want;
run;
Message was edited by: James Willis If the language(n) needs to be checked in all lang(n) fields, then the do statement will need to be built differently.
If you really only have 3 variables, you could just do it the simple way.
data want;
set have;
if language2=language1 then language2=' ';
if language3 = language2 then language3=' ';
if language3=language1 then language3=' ';
if language2=' ' then language2=language3;
run;
If you have more than 3 variables, then arrays might be a better choice.
Thank you everybody for your precious help
data have; format name $16. Language1 $2. Language2 $2. Language3 $2.; input name Language1 Language2 Language3; datalines; Tom EN EN IT MARK ES ES EN ; run; data want(drop=n i); set have; array lan{*} $ lang: ; array x{100} $ 32 _temporary_; n=0; do i=1 to dim(lan); if lan{i} not in x then do;n+1;x{n}=lan{i};end; end; call missing(of lan{*}); do i=1 to n; lan{i}=x{i}; end; run;
Xia Keshan
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.