Append rows into one Row with | delimiter

Accepted Solution Solved
Reply
Super Contributor
Posts: 418
Accepted Solution

Append rows into one Row with | delimiter

Hello everyone. I have been tasked with taking one to many data (Loans to Exceptions) and then concatenating every field in the dataset into one row, with each distinct row value separated by a delimiter (lets say Pipe).

Ex:

If the original data looked like the following.

data have;

   infile datalines delimiter=',';

   input Loan $ State $ ExceptionType $ CategoryDescription $ helpful $;

   datalines;                    

1,Ak,Bk35,Bankrupt,Yes

1,Ak,FC325,Process,Yes

1,AK,CK35,Manual,No

2,Ak,Bk35,Bankrupt,No

2,Ak,235,,Yes

2,AK,CK35,Manual,No

;

I would need it to look as follows.

data Need;

   infile datalines delimiter=',';

   informat loan $100.;

   informat state $100.;

   informat ExceptionType $100.;

   informat CategoryDescription $100.;

   informat helpful $100.;

   format loan $100.;

   format state $100.;

   format ExceptionType $100.;

   format CategoryDescription $100.;

   format helpful $100.;

 

   input Loan $ State $ ExceptionType $ CategoryDescription $ helpful $;

   datalines;  

1|1|1,Ak|Ak|Ak,Bk35|FC325|CK35,Bankrupt|Process|Manual,Yes|Yes|No               

2|2|2,Ak|Ak|Ak,Bk35|235|CK35,Bankrupt||Manual,No|Yes|No      

;

run;

Please note the field "categorydescription" has two delimiters next to eachother for loan number 2.

is there a simple way to do this in sas? I would thinkg maybe i would do a by grouping by Loan, and then just append new variables onto a list of previous variables, and then output the variable at the end of the by grouping.

I am struggling onw how to do this on one variable, but then also how I would incorporate it to make it dynamic enough to occur on EVERY column within the dataset (there are 50-100+ columns).

Any and all help is appreciated!

Thanks

Brandon


Accepted Solutions
Solution
‎04-22-2014 01:28 PM
Respected Advisor
Posts: 3,156

Re: Append rows into one Row with | delimiter

Posted in reply to Anotherdream

Here only one representing variable is shown, you can easily figure out other variables as well as 'rename' if want.

data have;

infile datalines dsd truncover;

input Loan $ State $ ExceptionType $ CategoryDescription $ helpful $;

datalines;                    

1,Ak,Bk35,Bankrupt,Yes

1,Ak,FC325,Process,Yes

1,AK,CK35,Manual,No

2,Ak,Bk35,Bankrupt,No

2,Ak,235,,Yes

2,AK,CK35,Manual,No

;

data want;

length _loan _state _exceptiontype _CategoryDescription _helpful $ 100;

do _n_=1 by 1 until (last.loan);

set have;

by loan notsorted;

     if _n_=1 then _CategoryDescription=CategoryDescription;

else _CategoryDescription=cats(_CategoryDescription,'|',CategoryDescription);

end;

run;

Good Luck,

Haikuo

Update: Array can help you with multiple variables.

data want;

length new_loan new_state new_exceptiontype new_CategoryDescription new_helpful $ 100;

array new new_:;

do _n_=1 by 1 until (last.loan);

set have;

array old loan--helpful;

by loan notsorted;

     do over new;

     if _n_=1 then new=old;

else new=cats(new,'|',old);

     end;

end;

keep new_:;

run;

View solution in original post


All Replies
Solution
‎04-22-2014 01:28 PM
Respected Advisor
Posts: 3,156

Re: Append rows into one Row with | delimiter

Posted in reply to Anotherdream

Here only one representing variable is shown, you can easily figure out other variables as well as 'rename' if want.

data have;

infile datalines dsd truncover;

input Loan $ State $ ExceptionType $ CategoryDescription $ helpful $;

datalines;                    

1,Ak,Bk35,Bankrupt,Yes

1,Ak,FC325,Process,Yes

1,AK,CK35,Manual,No

2,Ak,Bk35,Bankrupt,No

2,Ak,235,,Yes

2,AK,CK35,Manual,No

;

data want;

length _loan _state _exceptiontype _CategoryDescription _helpful $ 100;

do _n_=1 by 1 until (last.loan);

set have;

by loan notsorted;

     if _n_=1 then _CategoryDescription=CategoryDescription;

else _CategoryDescription=cats(_CategoryDescription,'|',CategoryDescription);

end;

run;

Good Luck,

Haikuo

Update: Array can help you with multiple variables.

data want;

length new_loan new_state new_exceptiontype new_CategoryDescription new_helpful $ 100;

array new new_:;

do _n_=1 by 1 until (last.loan);

set have;

array old loan--helpful;

by loan notsorted;

     do over new;

     if _n_=1 then new=old;

else new=cats(new,'|',old);

     end;

end;

keep new_:;

run;

Super Contributor
Posts: 418

Re: Append rows into one Row with | delimiter

Hello again Hai.Kuo. As always thanks for your help.  I'm guessing I would just take this code and wrap the If statements into some macro strings, along with the dynamic rename and drop statements. That part I can do!

Could you explain why your do loop is outside of the set loop? I actually tried something very similar to this (in my mind I thought they would be similar) and it doesn't work.... I'm not sure why this one does not and yours does, as it appears they are doing the same thing... (Or so I thought).

I know that I am reading in the dataset, and the way it is set up, my code is saying (I think) when its the first observation by loan, the temporary variable _categorydescrption is equal to the description... Else it's equal to itself (from the last row) and the new category description concatenated.

The issue is the retain seems to be pulling down the category description into every row, and the else statement is basically ignored. I am not sure why.

data notsurewhydoesntwork;

set have;

by loan;

retain _CategoryDescription;

if first.loan then _CategoryDescription=CategoryDescription;

else _CategoryDescription=cats(_CategoryDescription,'|',CategoryDescription);

run;

Respected Advisor
Posts: 3,156

Re: Append rows into one Row with | delimiter

Posted in reply to Anotherdream

Length. you have it truncated. As for the do loop wrapping set statement, it deserves a much broad topic, do a google search on "SAS DOW".

data notsurewhydoesntwork;

set have;

by loan;

length _CategoryDescription $ 100;

retain _CategoryDescription;

if first.loan then _CategoryDescription=CategoryDescription;

else _CategoryDescription=cats(_CategoryDescription,'|',CategoryDescription);

if last.loan then output;

run;

Haikuo

Super Contributor
Posts: 418

Re: Append rows into one Row with | delimiter

Posted in reply to Anotherdream

Wow I feel stupid after that mistake. Okay so apparently I was onto the correct idea in my code. THat makes me feel a little bit better.

Is it safe to assume that your methodology of putting the do loop outside of the set statement is more efficient than my coding practice? (i would add an "if last.loan then output").

If so then I think both codes do the same thing and I will use yours,as this is going to be run on a massive dataset, so even a small amount of efficiency gain is a huge help to me.

As always thanks. I will mark your answer as correct!

Respected Advisor
Posts: 3,156

Re: Append rows into one Row with | delimiter

Posted in reply to Anotherdream

Quote: "Is it safe to assume that your methodology of putting the do loop outside of the set statement is more efficient than my coding practice?"

That I really can't comment. It seems to me they should be very close in term of efficiency. I suggest you could do a benchmark on your real data. The reason I use DOW is 1) habit.  2) I don't need to remember retaining all the external variables within the loop, but again I often forget to close the loop anyway. 3) It sometimes gives me more maneuverability.

Good Luck,

Haikuo

Super Contributor
Posts: 418

Re: Append rows into one Row with | delimiter

Posted in reply to Anotherdream

Got ya. So really point #2 is a very valid benefit to your form of code (and I imagine would thus make it more efficient theoritically at least) because you don't have to define the retain statement for all of the variables.

Alright well I learned a lot from this, and time to go do some research on the DOW you mentioned.

Thanks again for taking the time to help "beginners" like me!

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 6 replies
  • 248 views
  • 2 likes
  • 2 in conversation