SAS combining a variable of multiple observations into a single observation

Question

In SAS, I want to combien the variable city of each county, here is my data:

county       city
 USA          LA
 USA       New York
France       Paris
France       Nice

And I want to create a new variable Allcity which contains all the city of each county:

county         Allcity
 USA          LA, New York
France       Paris, Nice

I only know how to combine two variables by using city||', '||city, but I don't know how to combien multiple observarions into a single ovservation.

udden2903 udden2903 · Accepted Answer · 2016-06-23T09:59:03

data have;
    infile datalines delimiter=',';
    input Country $ City $;
    datalines;
USA,LA
USA,New York
France,Paris
France,Nice
;
run;

The following code should help you:

proc sort data=have;
    by country city;
run;

data want (drop=city);
    do until (last.country);
        set have;
        by country;
        length allcity $100;
        allcity=catx(', ',allcity,city);
    end;
run;

SAS combining a variable of multiple observations into a single observation

1 Answers