combine observations into one with carriage returns

[arde]

Hi,

I would like to combine observations and display them with carriage returns:

have:

Country	Line	City
England	1	London
France	1	Paris
England	2	Liverpool
France	2	Marseille

want:

Country	Combined
England	Line 1 - London Line 2 - Liverpool
France	Line 1 - Paris Line 2 - Marseille

thank you

[Tom]

Display them where?

The tool you are using to display the data makes a difference.

For example PROC REPORT supports the SPLIT character for normal test output but with ODS type output you might need to use other methods.

https://communities.sas.com/t5/SAS-Programming/How-to-split-a-variable-string-in-proc-report/td-p/73...

[ballardw]

Also the combination of output generator and file destination can impact such things. Proc Report writing to Excel may make additional "breaks" in the data resulting in output that looks more like

Line 1

- London
Line 2

- Liverpool

I realize that you do not actually want the text "Line 1" in your result. However the approach used if your have longer text with spaces may attempt to break in more places than your desired location. So details do matter such as overall length of text, output generator and file type. Note: if the data set is used for further analysis or manipulation this particular activity may make the remainder of your tasks much harder. For example, what if there are any other variables in the data? What should happen with them?

[Ksharp · Accepted solution]

data have;
infile cards expandtabs;
input Country $ Line City :$20.;
cards;
England 1 London
France 1 Paris
England 2 Liverpool
France 2 Marseille
;

proc sort data=have out=temp;
by Country  Line;
run;
data want;
 do until(last.Country);
  set temp;
  by Country;
  length combine $ 80;
  combine=catx(' (*ESC*)n ',combine,catx(' ','Line',Line,'-',City));
 end;
 keep Country combine;
run;

proc report data=want nowd;run;

[arde]

thank you so much!