Building on this. The city dataset is a "tall data set" that requires a transpose to get it to be in the form of the original post. - But - if each account ( ID, etc) has variable number of cities we will need to handle that too.
PROC SQL;
CREATE TABLE WORK.QUERY_FOR_SHOES AS
SELECT DISTINCT t1.Region,
/* Subsidiary, ** First three characters** */
(UPCASE(substr(t1.Subsidiary,1,3))) AS Subsidiary
FROM SASHELP.SHOES t1;
QUIT;
PROC TRANSPOSE DATA=WORK.QUERY_FOR_SHOES
OUT=WORK.Transposed(DROP=_:)
PREFIX=City_
;
BY Region;
VAR Subsidiary;
RUN; QUIT;
data want;
set Transposed;
drop city_: ;
array city $ city_:;
length list_city $200;
list_city=" ";
do over city;
if not missing(city)
then list_city=catx(', ',list_city,city);
end;
run;
Since we dont "have" example data to work with, I made some data from SASHELP.SHOES.
... View more
