topic Re: How to concatenate multiple rows into one row in SAS DataFlux Data Management Studio ? in New SAS User

How to concatenate multiple rows into one row in SAS DataFlux Data Management Studio ?

ondrejblasko — Wed, 20 Nov 2019 10:45:31 GMT

Hello I am having trouble grouping/concentrating multiple rows into one in DataFlux using group column.

My input data looks like this :

Data INPUT
group	value
1	abc
1	def
2	ghi
2	jkl
2	mno
3	pqr

and I need to get result like this:

Data OUTPUT
group	value
1	abc, def
2	ghi, jkl, mno
3	pqr

I found many solutions for this situations for other SAS product, but I can't find one for SAS DataFlux.

I was looking for group by and transpose + concentrate options, but i was unable to find the right one.

Any suggestions how to resolve this ?

Thank you

Re: How to concatenate multiple rows into one row in SAS DataFlux Data Management Studio ?

PeterClemmensen — Wed, 20 Nov 2019 11:06:13 GMT

data have;
input group value $;
datalines;
1 abc
1 def
2 ghi
2 jkl
2 mno
3 pqr
;

data want(drop=value);
    do until (last.group);
        set have;
        length v $ 200;
        by group;
        v=catx(', ', v, value);
    end;
run;

Result:

group  v
1      abc, def
2      ghi, jkl, mno
3      pqr

Re: How to concatenate multiple rows into one row in SAS DataFlux Data Management Studio ?

Kurt_Bremser — Wed, 20 Nov 2019 11:15:01 GMT

This is how I would do it in SAS code:

data have;
input group value :$3.;
datalines;
1 abc
1 def
2 ghi
2 jkl
2 mno
3 pqr
;

/* determine the length needed for the concatenation */
proc sql noprint;
select max(count) * 4 - 1 into :length_needed
from (select count(*) as count length=3 from have group by group);
quit;

data want;
set have (rename=(value=_value));
by group;
length value $&length_needed.;
retain value;
if first.group
then value = _value;
else value = catx(',',value,_value);
if last.group;
drop _value;
run;

Re: How to concatenate multiple rows into one row in SAS DataFlux Data Management Studio ?

ed_sas_member — Wed, 20 Nov 2019 12:31:23 GMT

Another possibility:

/*Retrieve the max number of elements to be concatenated in a macrovariable &nbcol*/
proc sql;
	select max(frequency)
	into: nbcol
	from (select count(value) as frequency
		  from have
		  group by group);
quit;

data want;
	set have;

	/*Transpose all elements in as many columns as needed using an array*/
	array value_(&nbcol) $;

	by group;
	retain value_;
		
	if first.group then do;
			count=0;
			call missing(of value_(*));
		end;
	count+1;

	do i=1 to dim(value_);
		value_(count) = value;

	end;

	/*Concatenate elements in the same variable*/
	if last.group then do;
			value_cat = catx(', ',of value_(*));
			output;
		end;

	keep group value_cat;

run;

proc print data=want;
	id group;
run;

Best,

Re: How to concatenate multiple rows into one row in SAS DataFlux Data Management Studio ?

ondrejblasko — Wed, 20 Nov 2019 12:39:18 GMT

Thank You guys, but unfortunately I can't execute SAS code in SAS DataFlux Data Management Studio (at least i don't know how yet).

Re: How to concatenate multiple rows into one row in SAS DataFlux Data Management Studio ?

Kurt_Bremser — Wed, 20 Nov 2019 12:44:57 GMT

I'm not a DI User, but isn't there a User Written Transformation?

Re: How to concatenate multiple rows into one row in SAS DataFlux Data Management Studio ?

novinosrin — Wed, 20 Nov 2019 12:45:21 GMT

data have;
input group value :$3.;
datalines;
1 abc
1 def
2 ghi
2 jkl
2 mno
3 pqr
;

proc transpose data=have out=temp(drop=_name_);
by group;
var value;
run;

data want;
set temp;
length value $100;
value=catx(', ',of col:);
drop col:;
run;

Re: How to concatenate multiple rows into one row in SAS DataFlux Data Management Studio ?

ondrejblasko — Wed, 20 Nov 2019 13:28:26 GMT

There is so called 'Expression node' but it uses its custom language (Expression Engine Language EEL).

Documentation of EEL is here:https://support.sas.com/documentation/onlinedoc/dfdmstudio/2.3/dfU_ELRG.pdf

Re: How to concatenate multiple rows into one row in SAS DataFlux Data Management Studio ?

ballardw — Wed, 20 Nov 2019 15:56:03 GMT

You also might get an alternate approach if you can describe what advantages come from having multiple values in a single variable.

There are also some details that may have been left out such as what is the result for :

Data INPUT
group	value
1	abc
1	def
1      abc

Data INPUT
group	value
1	abc
1	def
1
2	ghi
2	jkl
2	mno
3	pqr

And should there be any imposed order on the result such that if you have data like

Data INPUT
group	value
1	abc
1	def
2	def
2	abc
2	mno
3	pqr

Should the result for group 1 and group 2 be the same?

Does the number of potential values vary for each group as you use this process? What might happen when a new value is associated with group 1? Will any of the following processing need to change?

If it is convenience for a person to read then there may be a report solution that provides such without actually transforming data.