Concatenate information from data in long format

[Natalie111]

Hi, I am trying to reorganise my dataset; the original dataset is in the following format (after sorting first by var1 and then by date):

data WORK;

input var1 date var2;

datalines;

1        d1       a   

1        d1       b   

1        d2       a      

2        d3       c

3        d4       a   

3        d5       a   

3        d5       b      

3        d5       c

;

The output dataset should look like:

var1  date    var2* 

1        d1       a,b   

1        d2       a   

2        d3       c

3        d4       a   

3        d5       a,b,c  

In a nutshell, I would like to keep only the unique combinations of var1 and date and concatenate information provided by var2 into a single new variable, i.e. var2*. 

I have tried different things but nothing seems to work so far as it should be. Could anyone please help?

Thanks!!

[PaigeMiller]

PROC TRANSPOSE is a simple way to do this, but it will produce three columns that contain original variable var2, rather than a text string as you have shown like a,b,c. I'd recommend the former, because I don't really see any easy way to make use of text string like a,b,c in the future.

data have;
input var1 date $ var2 $;
datalines;
1        d1       a   
1        d1       b   
1        d2       a      
2        d3       c
3        d4       a   
3        d5       a   
3        d5       b      
3        d5       c
;

proc transpose data=have out=want(drop=_name_) prefix=var2_;
	by var1 date;
	var var2;
run;

[PaigeMiller]

Adding

If you really want the text string a,b,c rather than the three columns, after doing PROC TRANSPOSE use:

data want2;
	set want;
	var2=catx(',',of var2_:);
	drop var2_:;
run;

[PeterClemmensen · Accepted solution]

Welcome to the SAS Community 🙂

data want;
	set work;
	by var1 date;

	if first.date then _var2=var2;
	else _var2=catx(',', _var2, var2);

	retain _var2;
	if last.date;
run;

[Natalie111]

Thank you!