topic Re: Concatenating strings from multiple rows into single row in SAS Programming

Concatenating strings from multiple rows into single row

Ronein — Sun, 07 Apr 2019 11:00:11 GMT

Hello

I want to Concatenating strings from multiple rows into single row in table.

I expect to get in last row under months_list column the value 1808+1809+1810+1811+1812+1901.

But I don't get this desired result.

May anyone help and explain what is the problem with code?

Data tbl1;
input month $;
cards;
1808
1809
1810
1811
1812
1901
;
run;


data tbl2;
  length months_list  $ 50;
  set tbl2;
  by month;
  retain months_list;
  if first.month_Yadua then months_list = month;
  else months_list = catx('+', months_list, month);
run;

Re: Concatenating strings from multiple rows into single row

Ksharp — Sun, 07 Apr 2019 11:10:19 GMT

Data tbl1;
input month $;
cards;
1808
1809
1810
1811
1812
1901
;
run;


data tbl2;
  length months_list  $ 50;
  set tbl1;
  retain months_list;
  months_list = catx('+', months_list, month);
run;

Re: Concatenating strings from multiple rows into single row

Ronein — Sun, 07 Apr 2019 11:11:22 GMT

Can you please explain why don;t we need to use first statement?

Re: Concatenating strings from multiple rows into single row

Kurt_Bremser — Sun, 07 Apr 2019 11:22:19 GMT

@Ronein wrote:

Can you please explain why don;t we need to use first statement?

Because you do not want to treat every month as a single group. You want to concatenate ALL months.

Re: Concatenating strings from multiple rows into single row

Shmuel — Sun, 07 Apr 2019 16:40:08 GMT

Please pay attention to notes, to your code:

data tbl2;
  length months_list  $ 50;
  set tbl2;   /* <<<< should be tbl1, same name as created in previous step */ 
  by month;
  retain months_list;   /* <<< default value is an empty field, so no need to assign
                            specific value first time */
  if first.month_Yadua then months_list = month;  /* month_Yadua is an undefined variable
                                                     you could use first.month (asyou defined by month )
                                                     but then
                                                     each row will be "first" and no concatenation */ 
  else months_list = catx('+', months_list, month);
run;

Re: Concatenating strings from multiple rows into single row

ballardw — Mon, 08 Apr 2019 15:25:33 GMT

@Ronein wrote:

Can you please explain why don;t we need to use first statement?

For one thing it references a variable that does not exist in your example data. The FIRST. syntax also expects that the variable appears on a BY statement. Since you don't have a variable month_Yadua in the example we get an uninitialized variable note and has no effect on the output