topic Re: how to check if multiple variables for a single observation/record has the same value in SAS Programming

how to check if multiple variables for a single observation/record has the same value

abhisas1 — Fri, 02 Sep 2022 19:41:46 GMT

I have multiple variables and I want to concatenate all the the values in the variables and i do not want same value twice.

In the below example i want to create new variable called var6 by concatenating all the values from var1 to var5 for each ID. but i do not want a value repeated. for instance, for ID 1 as advil twice. but in var6 i want it to be seen only once . ex: Var6 = mic,advil,keyboard,mouse

data chk;
input id var1$ var2$ var3$ var4$ var5$;

datalines;
1 mic advil keyboard advil mouse
2 mouse mouse keyboard perfume notebook
3 book wire plug desktop charger
4 mobile laptop mouse desktop phone
;
run;

Thank you in advance.

Re: how to check if multiple variables for a single observation/record has the same value

Reeza — Fri, 02 Sep 2022 21:02:56 GMT

Does order matter?

data want;
set chk;

array _v(*) var:;
length want $200.;

do i=1 to dim(_v);
if whichc(_v(i), of _v(*)) = i then want=catx(", ", want, _v(i));
end;


run;

@abhisas1 wrote:

I have multiple variables and I want to concatenate all the the values in the variables and i do not want same value twice.

In the below example i want to create new variable called var6 by concatenating all the values from var1 to var5 for each ID. but i do not want a value repeated. for instance, for ID 1 as advil twice. but in var6 i want it to be seen only once . ex: Var6 = mic,advil,keyboard,mouse

data chk;
input id var1$ var2$ var3$ var4$ var5$;

datalines;
1 mic advil keyboard advil mouse
2 mouse mouse keyboard perfume notebook
3 book wire plug desktop charger
4 mobile laptop mouse desktop phone
;
run;

Thank you in advance.

Re: how to check if multiple variables for a single observation/record has the same value

abhisas1 — Fri, 02 Sep 2022 20:56:30 GMT

yes ! order matters.

Re: how to check if multiple variables for a single observation/record has the same value

Reeza — Fri, 02 Sep 2022 21:01:38 GMT

Solution above incorporates order.

Re: how to check if multiple variables for a single observation/record has the same value

ballardw — Fri, 02 Sep 2022 21:14:33 GMT

@abhisas1 wrote:
yes ! order matters.

If there is a specific order that you have in mind then you need to provide it.

If the specific order is not critical in the final as long as it is consistent with the words then this may do what you want or get you started.

First copy the existing values into new variables (prevents loss of original data if there is a logic problem) in an array; sort the array; then remove adjacent duplicates. You didn't describe a delimiter to separate the values so I chose a comma. The catx function nicely drops any missing values when concatenating this way.

After fairly sure that the result is as desired drop the loop counter and the array. Could have made X temporary but might want the information for debugging.

data chk;
   input id var1$ var2$ var3$ var4$ var5$;
   array v(*) var:;
   array x(5) $ 8;
   do i=1 to dim(x);
      x[i]=v[i];
   end;
   call sortc(of x(*));
   do i=1 to (dim(x) - 1);
      if x[i]=x[i+1] then call missing(x[i]);
   end;
   var6 = catx(',',of x(*));
   drop i x:;
datalines;
1 mic advil keyboard advil mouse
2 mouse mouse keyboard perfume notebook
3 book wire plug desktop charger
4 mobile laptop mouse desktop phone
;
run;

Re: how to check if multiple variables for a single observation/record has the same value

abhisas1 — Fri, 02 Sep 2022 23:05:10 GMT

worked perfectly. Thank you so much for detailed step by step explanation.

Re: how to check if multiple variables for a single observation/record has the same value

Ksharp — Sat, 03 Sep 2022 09:56:17 GMT

data chk;
   input id var1$ var2$ var3$ var4$ var5$;
   array v(*) var:;
   array x(5) $ 8;
   n=0;
   do i=1 to dim(x);
      if v[i] not in x then do;n+1;x[n]=v[i];end;
   end;
   var6 = catx(',',of x(*));
   drop i x: n;
datalines;
1 mic advil keyboard advil mouse
2 mouse mouse keyboard perfume notebook
3 book wire plug desktop charger
4 mobile laptop mouse desktop phone
;
run;