Results set consolidation

Reply
New Contributor
Posts: 3

Results set consolidation

How can I go from mulitple rows of output where all columns match except one

Header 1Header 2Header 3Header 4
data1data2data3123
data1data2data3456
data1data2data3789

To a single row of output with comma separated results for the last row i.e.

Header 1Header 2Header 3Header 4
data1data2data3123,456,789
Super User
Posts: 10,516

Re: Results set consolidation

Are the values in header4 actually numeric values or character? If the values are character to any of them already have one or more commas? Would the presence of those commas cause any confusion the final result?

Since the result is likely to be a character value, do you have any idea how many may be combined and what the maximum combined length could be? If you have combinations of Headers1 to 3 with 1000s of rows in common then combining all of header4 into a single variable may exceed the length of a variable SAS can store.

data want;

     set have;

     by header1 header2 header3;

     length combined $ 32767; /* longest value*/

     retain combined;

     if first.header3 then combined=header4;

     else combined=catx(',',combined,header4);

     if last.header3 then output;

run;

may work if the headers are sorted. If not the notsorted option on the by statement may work.

New Contributor
Posts: 3

Re: Results set consolidation

Outstanding all...  Thanks for the feedback.  I will give an update as soon as possible...

Valued Guide
Posts: 858

Re: Results set consolidation

Here you go:

data have;

infile cards dsd;

input Header1 $    Header2 $ Header3 $    Header4 $;

cards;

data1,data2,data3,123

data1,data2,data3,456

data1,data2,data3,789

;

run;

proc transpose data= have out= have1(drop=_name_);by Header1;var Header4;

proc sql;

create table finally as

select distinct a.header1,a.header2,a.header3,catx(',',b.col1,b.col2,b.col3) as header4

from have a left join

     have1 b on

a.header1 = b.header1;

Super User
Posts: 9,682

Re: Results set consolidation

data have;

infile cards dsd;

input Header1 $    Header2 $ Header3 $    Header4 $;

cards;

data1,data2,data3,123

data1,data2,data3,456

data1,data2,data3,789

;

run;

data want ;

set have;

by Header1    Header2  Header3;

length H4 $ 2000;

retain H4;

H4=catx(',',H4,Header4);

if last.Header3 then do;output;call missing(H4);end;

drop Header4;

run;

Ask a Question
Discussion stats
  • 4 replies
  • 177 views
  • 0 likes
  • 4 in conversation