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 Custom
erIP'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;
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
This has been discussed so many times, i am puzzled that you have found nothing adaptable to your needs.
I would:
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.
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
Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
Register now!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Ready to level-up your skills? Choose your own adventure.