I have a dataset that have one id and multiple block and street names.I need to place together all the blocks and street names of an id together by concatenating separated by a comma. The dataset looks like this.
ID block street name Ttk1 104A John's street 104B John's street Ttk2 614 Mary street 615 Mary street 616 Mary street 617 Mary street 618 Mary street Ttk3 415 Brooklyn street Ttk4 202 Nashville street 203 Nashville street
The final dataset should look like this. Ttk1 104A John's street,104B John's street Ttk2 614 Mary street,615 Mary street,616 Mary street, 617 Mary street,618 Mary street Ttk3 415 Brooklyn street Ttk4 202 Nashville street,203 Nashville street
I have tried CATX but couldn't get these results. Any sugestions on how this can be achieved?
Please post test data in the form of a datastep in future, as such this is untested:
data want;
set have;
length full $2000;
retain full;
by id;
full=ifc(first.id,full,catx(",",full,block_street_name);
run;
Ready to join fellow brilliant minds for the SAS Hackathon?
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.