topic Re: combine observations into one with carriage returns in SAS Programming

combine observations into one with carriage returns

arde — Mon, 24 Oct 2022 14:21:53 GMT

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

Re: combine observations into one with carriage returns

Tom — Mon, 24 Oct 2022 14:45:30 GMT

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/738283

Re: combine observations into one with carriage returns

ballardw — Mon, 24 Oct 2022 15:39:29 GMT

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?

Re: combine observations into one with carriage returns

Ksharp — Tue, 25 Oct 2022 11:47:50 GMT

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;

Re: combine observations into one with carriage returns

arde — Tue, 25 Oct 2022 13:13:23 GMT

thank you so much!