Concatenate multiple rows into a single value

[mkaur89]

Hi there!

I'm having a problem with concatenating a series of rows into a single row based on a group within a SAS dataset.

Basically a simplified version of what I want is to be able to is start with something like this:

PROC SQL;
CREATE TABLE WORK.Renewal AS

SELECT
/* Policy Number */
(CAT(t1.BRANCH_NBR, t1.DEC_NBR, t1.PAK_POLCY_NBR)) AS 'Policy Number'n,
t1.POLCY_PREFX_ID

this will give something like 

 

I'm trying to get something like this:

 

I looked at this post and it has the basic concept of what i'm wanting, but not exactly. 

https://communities.sas.com/t5/SAS-Programming/Concatenate-multiple-rows-into-a-single-value/td-p/13...

[Reeza]

You cannot do this easily in SQL, you need a data step, and the solution is the same as in the example you've linked to. SQL doesn't absolutely maintain row orders, so that's one reason not to use SQL. If you're using a server, you can use Pass Through and either a PIVOT or SUMMARIZE task to achieve the same result but that would depend on your RDBMS.

[PeterClemmensen]

This is not a job for PROC SQL. Do like this

data have;
input PolicyNumber $ Policy_Prefix_ID $;
datalines;
123458 ABC
123458 BCD
123458 CDF
199564 ABC
199564 WFD
199564 GHB
185284 XXX
153485 ABC
;

data want;
   set have(rename=(Policy_Prefix_ID=ppid));
   length Policy_Prefix_ID $100;
   by PolicyNumber notsorted;

   if first.PolicyNumber then Policy_Prefix_ID="";

   Policy_Prefix_ID=cats(Policy_Prefix_ID, ppid);

   if last.PolicyNumber;
   retain Policy_Prefix_ID;
   drop ppid;
run;

[ballardw]

I thing instead of

Policy_Prefix_ID=cats(Policy_Prefix_ID, ppid);

that

Policy_Prefix_ID=catx(',',Policy_Prefix_ID, ppid);

comes closer to requested result.

Note that you should have some idea how many items and their lengths to set the length of the result. Don't forget to count the commas.

And again, why? If this for anything more than a marginally hard to read report column further processing is likely to be a headache.

[mkaur89]

Adding the commas did not really help. It just added that many comes to the front of the row. I would want the commas separating the prefixes. 

My company is in the process of converting all of our data extraction queries to SAS. A large number of our queries are in Hyperion and I've come across this scenario in multiple queries. Our data in being extracted from a teradata server so depending on the filters i input i have no idea how many results i will end up getting. 

[ballardw]

---

@mkaur89 wrote:

Adding the commas did not really help. It just added that many comes to the front of the row. I would want the commas separating the prefixes. 

My company is in the process of converting all of our data extraction queries to SAS. A large number of our queries are in Hyperion and I've come across this scenario in multiple queries. Our data in being extracted from a teradata server so depending on the filters i input i have no idea how many results i will end up getting. 

---

SHOW the code you actually ran.

The presence of many commas as you show indicates that your field has missing values for some of the data. So you need to decide what to do about the missing values. Test if missing and not concatenate would be one option but you know more of what you need.

Here is one way to find out how many levels of your variable you may need:

proc sql;
   select max(countage) as maxvars
   from (select sex, count(age) as countage from 
            (select distinct sex,age from sashelp.class)
         group by sex
        )
   ;
quit;

Maxvars would have the maximum count of the variable age associated with the different levels of the Sex variable in this set.

[Reeza]

---

@mkaur89 wrote:

Adding the commas did not really help. It just added that many comes to the front of the row. I would want the commas separating the prefixes. 

My company is in the process of converting all of our data extraction queries to SAS. A large number of our queries are in Hyperion and I've come across this scenario in multiple queries. Our data in being extracted from a teradata server so depending on the filters i input i have no idea how many results i will end up getting. 

---

The approach used in the data doesn't care how many records there are for each ID. It does it automatically. Did you try any of those solutions, and if you did, please post the code that didn't work. 

If you're just learning, try it with example data first, even just running the code/solution from the other threads and then figure out how your data is different than the example to figure out where the issue is. 

[mkaur89]

I'm not sure how to go about doing what you just suggested. It seems like i would have to type out every results. The way i am getting my data is through a teradata server and it could be over a 100,000 results and i don't know how many prefix id's a policy might have. Knowing this information, would you still suggest using this method or should I focus on maybe creating a pivot? I'm really new to SAS (just started learning 2 weeks ago) and I am imagining a pivot similar to which i can create in excel. However, even an excel pivot can not get me all of the rows into a single cell. 

I'm used to using Hyperion and i was able to compute it using a simple formula: 

Sum(Policy_Prefx_ID, Policy_Number)