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
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.