Combining multiple rows of text into one in Proc sql

[BrianC]

I know there is a way in Proc sql to combine multiple rows of a text field into one? I have tried a lot of the sql examples eg sum(), string_agg, xml for path. It all doesn’t seem to work. I can do this in base SAS but I need the code in sql.

The data variables are id, date, orderno, line_order, result. ‘Result’ is the text field with multiple rows I need in one row and repeating id, orderno and date. The line_order variable gives the correct order of the rows in ascending. The lines can be as many as 30 rows of text. An id can occur multiple times but with a different orderno. Hope you can help me or point me in the right direction. Thanks!

[mkeintz]

Show us, in the form of a data step with sample data, both the BEFORE and AFTER datasets.  Help us help you.

[Tom]

Assuming you are talking about collapsing multiple observations into a single observation and not just removing some embedded line break characters in a character value on one observation, then the answer is No. 

Why would you want to use PROC SQL for this?

data want ;
do until (last.orderno);
  set have ;
  by id date orderno line_order ;
  length full_result $1000 ;
  full_result=catx(' ',full_result,result);
end;
  drop result ;
  rename line_order = number_of_lines ;
run;

[ed_sas_member]

Hi @BrianC 

Welcome to the community!

I don't think such a function is available in PROC SQL for now:

 https://communities.sas.com/t5/SASware-Ballot-Ideas/Add-GROUP-CONCAT-function-to-proc-sql/idi-p/323...

As you mention, it will be easier using a data step.

Could you please explain why this option is the only one for you?

Best,

[BrianC]

Thank you for the rapid responses!

The reason for sql is that the database is stored on teradata (over 30 mil obs) and so far every attempt to run a data step has taken so long that I have had to stop it. Proc sql (and sql in teradata studio) runs much more efficiently/faster. I am new to working with such large files and so if you have insight on how to work more efficiently with a data step over a teradata server, I am all for it. I am open to any solution that gets this done, efficiently! Thank you all again!

[Tom]

---

@BrianC wrote:
Thank you for the rapid responses!

The reason for sql is that the database is stored on teradata (over 30 mil obs) and so far every attempt to run a data step has taken so long that I have had to stop it. Proc sql (and sql in teradata studio) runs much more efficiently/faster. I am new to working with such large files and so if you have insight on how to work more efficiently with a data step over a teradata server, I am all for it. I am open to any solution that gets this done, efficiently! Thank you all again!

---

That is a different question, one that could be answered on a Teradata discussion forum.

Just use pass-thru SQL to run whatever Teradata code you want.  Will have the advantage of only returning to SAS the set of data you need.

proc sql;
connect to teradata ..... ;
create table want as select * from connection to teradata 
( ... put your Teradata code that uses Windowing Functions
   or Common Table Expressios (with keyword)
   or Recursion
   or other features that Teradata SQL supports 
 ...
);