topic Re: concatenate customers that ID belong to in SAS Programming

concatenate customers that ID belong to

Ronein — Thu, 12 Aug 2021 05:40:42 GMT

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;

Re: concatenate customers that ID belong to

andreas_lds — Thu, 12 Aug 2021 06:38:02 GMT

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

Re: concatenate customers that ID belong to

PaigeMiller — Thu, 12 Aug 2021 09:54:06 GMT

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.

Re: concatenate customers that ID belong to

jimbarbour — Thu, 12 Aug 2021 15:54:26 GMT

@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

Re: concatenate customers that ID belong to

Reeza — Thu, 12 Aug 2021 16:00:46 GMT

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