topic Concatenate information from data in long format in SAS Studio

Concatenate information from data in long format

Natalie111 — Wed, 21 Aug 2019 13:56:20 GMT

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!!

Re: Concatenate information from data in long format

PaigeMiller — Wed, 21 Aug 2019 14:11:16 GMT

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;

Re: Concatenate information from data in long format

PaigeMiller — Wed, 21 Aug 2019 14:23:16 GMT

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;

Re: Concatenate information from data in long format

PeterClemmensen — Wed, 21 Aug 2019 14:31:44 GMT

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;

Re: Concatenate information from data in long format

Natalie111 — Thu, 22 Aug 2019 09:25:50 GMT

Thank you!