topic Re: Duplicate numeric in column A, different character in column B in SAS Enterprise Guide

Duplicate numeric in column A, different character in column B

Niz88 — Thu, 26 Aug 2021 13:12:32 GMT

I have 2 column, A(C.A) and B(C.B)
C.A contains duplicate for eg. 1,1,1
C.B does not contain duplicate(a,b,c)
Table eg
C.A : A
C.A : B
C.A : C
How do i make it into
C.A : C.B(A,B,C)
C.A : C.B(A,B,C)
C.A : C.B(A,B,C)

Can anybody help please. Thank u.

Re: Duplicate numeric in column A, different character in column B

Sajid01 — Thu, 26 Aug 2021 13:54:08 GMT

Can you be more clear please.

Re: Duplicate numeric in column A, different character in column B

ballardw — Thu, 26 Aug 2021 14:21:41 GMT

Please provide an of your existing data in the form of a data step.

And please describe what you will do with the resulting data set that can't be done with it in the current form.

Re: Duplicate numeric in column A, different character in column B

Niz88 — Thu, 26 Aug 2021 14:22:46 GMT

Can sas do this
A B to A B
1 A 1 A,B and C
1 B 1 A,B and C
1 C 1 A,B and C
2 A 2 A and B
2 B 2 A and B

Re: Duplicate numeric in column A, different character in column B

Tom — Thu, 26 Aug 2021 14:43:07 GMT

What is your starting dataset? Show it as a data step that recreates it.

Is it something like:

data have;
  input a b $;
cards;
1 A
1 B
1 C
2 A
2 B
;

What is your desired output dataset? Is it something like this?

data want;
  input a b_list :$20.;
cards;
1 A,B,C
2 A,B
;

If so then try:

data want;
   length a 8 b_list $20 ;
   do until(last.a);
      set have;
      by a;
      b_list=catx(',',b_list,b);
   end;
   drop b;
run;

Re: Duplicate numeric in column A, different character in column B

ballardw — Thu, 26 Aug 2021 15:04:23 GMT

@Niz88 wrote:
Can sas do this
A B to A B
1 A 1 A,B and C
1 B 1 A,B and C
1 C 1 A,B and C
2 A 2 A and B
2 B 2 A and B

There is not way in that stuff above to tell what the starting variables are or what the value per variable might be. SAS works on variables.

Re: Duplicate numeric in column A, different character in column B

Niz88 — Thu, 26 Aug 2021 15:04:40 GMT

i want my desire input to be like

nO NAME TO NO NAME 1 A 1 A,B AND C 1 B 1 A,B AND C 1 C 1 A,B AND C 2 A 2 A AND B 2 B 2 A AND B Thanks for the reply. I am new to SAS. Normally i'll just import data and SAS will auto generate the code but i cant find a way to solve this problem. Need help from all who know how to use it.

Re: Duplicate numeric in column A, different character in column B

ballardw — Thu, 26 Aug 2021 15:40:10 GMT

@Niz88 wrote:

i want my desire input to be like

nO NAME TO NO NAME 1 A 1 A,B AND C 1 B 1 A,B AND C 1 C 1 A,B AND C 2 A 2 A AND B 2 B 2 A AND B Thanks for the reply. I am new to SAS. Normally i'll just import data and SAS will auto generate the code but i cant find a way to solve this problem. Need help from all who know how to use it.

If you are dealing with Proc Import or wizard creating the data you have an issue in that the existing NAME variable is not going to be long enough to hold 3 (or more) values.

Do you actually want the word AND in the data? That adds a certain amount of complication to the possible solutions.

Re: Duplicate numeric in column A, different character in column B

Niz88 — Thu, 26 Aug 2021 16:06:24 GMT

NO NAME TO NO NAME DATA 1 A 1 A,B,C 1 B 1 A,B,C 1 C 1 A,B,C 2 A 2 A,B 2 B 2 A,B thanks ballardw for the reply. ok,then i remove the word AND. I tried to use the advance expression to concatenate the data together but unable to do so. Will have to try tom code. I am trying to learn how to use the code.

Re: Duplicate numeric in column A, different character in column B

Tom — Thu, 26 Aug 2021 16:38:21 GMT

What is an "advanced expression"?

You are you using some type of point and click code generator in Enterprise Guide?

Re: Duplicate numeric in column A, different character in column B

ballardw — Thu, 26 Aug 2021 17:00:45 GMT

Perhaps, borrowing from @Tom

The first data step is just to build something similar to what you describe as your data.

The data set TEMP is code you would run against that data set to get the value list. Depending on the actual lengths of your values the length of the list variable may need to be much larger. This sort of thing is not normally built by point and click interfaces because it is a non-standard action and makes data that is often much harder to work with for some tasks.

Then the last step combines the two data sets.

data have;
  input a b $;
cards;
1 A
1 B
1 C
2 A
2 B
;

data temp;
   length a 8 b_list $20 ;
   do until(last.a);
      set have;
      by a;
      b_list=catx(',',b_list,b);
   end;
   drop b;
run;

data want;
   merge have
         temp
   ;
   by a;
run;

Still want to know what you expect to do with the combined values, especially when they are repeated.