topic Re: agegroup names concatinate in New SAS User

agegroup names concatinate

BrahmanandaRao — Wed, 20 Mar 2019 13:24:33 GMT

In sashelp.class

I want to cancatinate name those who have same age group

i want output as like below

Name	Sex	Age	Height	Weight	Same age_group _names
Joyce	F	11	51.3	50.5
Thomas	M	11	57.5	85	Joyce Thomas
James	M	12	57.3	83
Jane	F	12	59.8	84.5
John	M	12	59	99.5
Louise	F	12	56.3	77
Robert	M	12	64.8	128	James Jane John Louise Robert
Alice	F	13	56.5	84
Barbara	F	13	65.3	98
Jeffrey	M	13	62.5	84	Alice Barbara Jeffrey
Alfred	M	14	69	112.5
Carol	F	14	62.8	102.5
Henry	M	14	63.5	102.5
Judy	F	14	64.3	90	Alfred Carol Henry
Janet	F	15	62.5	112.5
Mary	F	15	66.5	112
Ronald	M	15	67	133
William	M	15	66.5	112	Janet Mary Ronald William
Philip	M	16	72	150	Philip

Re: agegroup names concatinate

novinosrin — Wed, 20 Mar 2019 13:32:02 GMT


proc sort data=sashelp.class out=have;
by age;
run;

data want;
do until(last.age);
set have;
by age;
length temp Same_age_group_names $100;
temp=catx(' ',temp,name);
if last.age then Same_age_group_names=temp;
output;
end;
drop temp;
run;

Re: agegroup names concatinate

BrahmanandaRao — Wed, 20 Mar 2019 13:34:28 GMT

Thank you very much

Re: agegroup names concatinate

Arjen — Wed, 20 Mar 2019 13:43:25 GMT

First you need to make sure the data set is sorted by Age and then use a Data step with a by clause. Example code with comments below:

* sort the input dataset by age and name (the latter is optional);
proc sort data=sashelp.class out=sortedbyage;
	by Age Name;
run;

* create a new dataset with concatenated names;
data concatenatednames;
	* use sorted dataset;
	set sortedbyage;
	* group by Age;
	by Age;
	* Same_age is the temporary variable for holding the concatenated names. Make it sufficiently long;
	length Same_age $ 80;
	* Same_age_group_names will hold the final value at the last record for a certain age;
	length Same_age_group_names $ 80;

	* Hold on to the value in the Same_age variable across observations;
	retain Same_age;
	if first.Age then
     do;
		* First record of a certain age, initialize the variable with the name of the student;
		Same_age = Name;
	 end;
	else 
	 do;
		* Not the first record, append the name to the Same_age variable;
		Same_age = CATX(" ", Same_age, Name);
	 end;
	if last.Age then
	 do;
	 	* Last record of a certain age. Save the concatenated string in the Same_age_group_names variable;
		Same_age_group_names = Same_age;
	 end;
	* We don't want Same_age to end up in our data, so drop it;
	drop Same_age;
run;

Re: agegroup names concatinate

Arjen — Wed, 20 Mar 2019 13:44:15 GMT

Beat me to it 🙂

Re: agegroup names concatinate

novinosrin — Wed, 20 Mar 2019 13:45:34 GMT

HI @Arjen Appreciate your traditional approach and nice comments. That's a virtue.

Re: agegroup names concatinate

Arjen — Wed, 20 Mar 2019 13:55:53 GMT

Hi @novinosrin.
Thanks. Like the do-until approach as well. Would never have thought of that.

Re: agegroup names concatinate

novinosrin — Wed, 20 Mar 2019 14:00:14 GMT

More fun before my coffee break

proc sort data=sashelp.class out=have;
by age;
run;
proc transpose data=have(keep=age name) out=_have(drop=_:);
by age;
var name;
run;
data want;
merge have _have;
by age;
length  Same_age_group_names $100;
if last.age then Same_age_group_names=catx(' ',of col:);
drop col:;
run;

Re: agegroup names concatinate

Arjen — Wed, 20 Mar 2019 14:02:54 GMT

Brilliant

Re: agegroup names concatinate

novinosrin — Wed, 20 Mar 2019 14:09:44 GMT

Park in temp array and concatenate all at once

proc sort data=sashelp.class out=have;
by age;
run;
data want;
array temp(999)$20 _temporary_;
call missing( of temp(*));
do _n_=1 by 1 until(last.age);
set have;
by age;
length  Same_age_group_names $100;
temp(_n_)=name;
if last.age then Same_age_group_names=catx(' ',of temp(*));
output;
end;
run;

Edited to add fogotten call missing

Re: agegroup names concatinate

Arjen — Wed, 20 Mar 2019 14:09:16 GMT

Anyone would be crazy not to love the versatility of the SAS programming language and the data step.

Re: agegroup names concatinate

novinosrin — Wed, 20 Mar 2019 14:11:19 GMT

Indeed sir @Arjen extremely addictive 🙂