concatenate customers that ID belong to

[Ronein]

Hello

User define customerIP in a macro variable.

In step1 we check which ID's belong to this customerIP.

Then we check for each ID (from step1) to which customerIP they belong.

I want to create a new field called vector  that will concatenate the CustomerIP's .

So the expected results is:

111 1234567,8273774

222 1234567,8765432,7654221

What is the way to get it please?

%let customerIP=1234567;
Data have;
input ID customerIP;
cards;
111 1234567
222 1234567
333 8788888
444 8798777
555 8765432
222 8765432
222 7654221
111 8273774
;
Run;

[andreas_lds]

This has been discussed so many times, i am puzzled that you have found nothing adaptable to your needs.

I would:

• sort have by ID
• use a data step if first/last id
• a retained variable to hold the list
• catx to fill the list
• output on last.id

[PaigeMiller]

To add to @andreas_lds comments, we have also discussed with you on many occasions that long is superior to wide, for almost anything you will do in SAS. Yet in the last few weeks, you are consistently asking to turn long data sets into wide. Perhaps you ought to rethink this.

[jimbarbour · Accepted solution]

@Ronein,

Well, first, I agree with @andreas_lds and @PaigeMiller that transforming from long to wide is generally a poor idea.  However, maybe it's for reporting or for export as a .csv file, so maybe it's worthwhile.

That said, here's one solution, below.  The Call Missing() is probably unnecessary but is there at least in part to make it clear that the IPs are reset whenever there is a new ID.  Below the code are the results.

%let CustomerIP=1234567;

Data have;
	input ID CustomerIP;
cards;
111 1234567
222 1234567
333 8788888
444 8798777
555 8765432
222 8765432
222 7654221
111 8273774
;
Run;

PROC	SORT	DATA=Have;
	BY	ID	CustomerIP;
RUN;

DATA	Want;
	DROP		CustomerIP;
	SET	Have;
		BY	ID	CustomerIP;
	LENGTH	IPs	$32767;
	RETAIN	IPs;

	IF	FIRST.ID		THEN
		DO;
			CALL		MISSING(IPs);
			IPs		=	STRIP(PUT(CustomerIP,8.));
		END;
	ELSE
		DO;
			IPs		=	CATS(IPs,',',PUT(CustomerIP,8.));
		END;

	IF	LAST.ID;
	IF	INDEX(IPs	,	"&CustomerIP");
RUN;

Results:

Jim

[Reeza]

https://gist.github.com/statgeek/d583cfa992bf56da51d435165b07e96a