topic Re: Concat the values in columns in SAS Programming

Concat the values in columns

SASdevAnneMarie — Thu, 11 Aug 2022 10:24:35 GMT

Hello Experts,

I would like to rearrange the columns in this way : I have the columns produit, nbr_code, lib_produit and I would like to create the column code_2 :

produit	nbr_code	lib_produit	code_2
A270	2	1819	A270\|A270S
A270S	2	1819	A270\|A270S
X256	1	PARTENAIRE	X256
K103	4	AEDE	K103\|M103S\|L279\|O479
M103S	4	AEDE	K103\|M103S\|L279\|O479
L279	4	AEDE	K103\|M103S\|L279\|O479
O479	4	AEDE	K103\|M103S\|L279\|O479

My code is :

data test2;
	set test;
	by lib_produit;
	length code_prod $200;
	retain code_prod;


			if produit='' then
				do;
					code_prod=' ';
					output;
				end;
			else
				do;
					if last.lib_produit then
		            do;
					code_2=catx('',code_2,produit);
					output;
				end;
				end;
	
run;

But I didn't obtain the desired result.

Thank you for your help.

Re: Concat the values in columns

PeterClemmensen — Thu, 11 Aug 2022 10:34:28 GMT

Try this

data have;
input produit $ nbr_code lib_produit :$20.;
datalines;
A270  2 1819       
A270S 2 1819       
X256  1 PARTENAIRE 
K103  4 AEDE       
M103S 4 AEDE       
L279  4 AEDE       
O479  4 AEDE       
;

data want;
   do until (last.lib_produit);
      set have;
      by lib_produit notsorted;
      length code_2 $200;
      code_2 = catx('|', code_2, produit);
   end;

   do until (last.lib_produit);
      set have;
      by lib_produit notsorted;
      output;
   end;
run;

Result:

produit  nbr_code  lib_produit  code_2
A270     2         1819         A270|A270S 
A270S    2         1819         A270|A270S 
X256     1         PARTENAIRE   X256 
K103     4         AEDE         K103|M103S|L279|O479 
M103S    4         AEDE         K103|M103S|L279|O479 
L279     4         AEDE         K103|M103S|L279|O479 
O479     4         AEDE         K103|M103S|L279|O479

Re: Concat the values in columns

ballardw — Thu, 11 Aug 2022 15:11:20 GMT

For one thing your code reuses a variable Code_2 that does not have a defined length, would get the length set from the first value assigned and then you are attempting to stuff 3 (or more) values into it. So very likely the length of the first use of Code_2 is not long enough to hold the additional text.

I think you meant

code_2=catx('',code_2,produit);

to be

code_prod=catx('|',code_prod,produit);

or set the length for code_2 instead of code_prod.

You also need to reset Code_prod (or Code_2) at the First.lib_produit to the first produit. Otherwise you are retaining and accumulating from the previous lib_produit group of values.

Second the data step processes things sequentially (in general) Since you are combining multiple observations of data once you build the total combined version you would have to merge that result back to the source data. Which is going to be fun as you don't show anything that is going to work real well for matching to the original data.

"Didn't obtain the desired result" is awful vague.

Are there errors in the log?: Post the code and log in a code box opened with the "</>" to maintain formatting of error messages.

No output? Post any log in a code box.

Unexpected output? Provide input data in the form of data step code pasted into a code box, the actual results and the expected results. Instructions here: https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-data-AKA-generate/ta-p/258712 will show how to turn an existing SAS data set into data step code that can be pasted into a forum code box using the "</>" icon or attached as text to show exactly what you have and that we can test code against.

Re: Concat the values in columns

SASdevAnneMarie — Fri, 12 Aug 2022 18:49:09 GMT

Thank you, Peter !
It works !

Re: Concat the values in columns

SASdevAnneMarie — Wed, 07 Sep 2022 09:02:50 GMT

Hi Peter,
Could you explain me please this part of the code :
do until (last.lib_produit);
set have;
by lib_produit notsorted;
output;
end;
Thank you !