topic Re: Combining multiple rows of text into one in Proc sql in SAS Programming

Combining multiple rows of text into one in Proc sql

BrianC — Sat, 14 Mar 2020 16:38:38 GMT

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!

Re: Combining multiple rows of text into one in Proc sql

mkeintz — Sat, 14 Mar 2020 16:57:20 GMT

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

Re: Combining multiple rows of text into one in Proc sql

Tom — Sat, 14 Mar 2020 17:07:11 GMT

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;

Re: Combining multiple rows of text into one in Proc sql

ed_sas_member — Sat, 14 Mar 2020 17:19:33 GMT

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

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,

Re: Combining multiple rows of text into one in Proc sql

BrianC — Sat, 14 Mar 2020 17:42:23 GMT

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!

Re: Combining multiple rows of text into one in Proc sql

Tom — Sat, 14 Mar 2020 17:47:28 GMT

@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 
 ...
);