BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
abhisas1
Fluorite | Level 6

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.

1 ACCEPTED SOLUTION

Accepted Solutions
ballardw
Super User

@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;

 

 

View solution in original post

6 REPLIES 6
Reeza
Super User

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.


 

ballardw
Super User

@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;

 

 

abhisas1
Fluorite | Level 6
worked perfectly. Thank you so much for detailed step by step explanation.
Ksharp
Super User
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;

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 6 replies
  • 1546 views
  • 2 likes
  • 4 in conversation