topic Re: group by with concatenate over rows in SAS Programming

group by with concatenate over rows

Ronein — Wed, 03 Aug 2022 06:42:39 GMT

Hello

I have a data set with loans information.

Each customer can appear in multiple rows (If he took multiple loans)..

I want to create a summary table that each customerID will appear in 1 row per month (YYMM).

In the wanted data set will have 3 columns:

month

Concatenate_Type_Amount

What is the way to create this data set please?


Data Loans;
input ID  Type amount month;
cards;
11111 712 1000 2201
11111 987 2000 2201
11111 712 3000 2201
11111 712 5000 2202
22222 712 4300 2201
22222 987 1500 2201
;
Run;

The wanted data set will have 3 rows:

/*Wanted table*/
/*11111 2201 (712-4000),(987-2000)*/
/*11111 2202 (712-5000)*/
/*22222 2201 (712-4300),(987-1500)*/

Re: group by with concatenate over rows

PeterClemmensen — Wed, 03 Aug 2022 08:44:00 GMT

Try this

Data Loans;
input ID  Type amount month;
cards;
11111 712 1000 2201
11111 987 2000 2201
11111 712 3000 2201
11111 712 5000 2202
22222 712 4300 2201
22222 987 1500 2201
;
Run;

proc summary data = Loans nway;
   class ID month type;
   var amount;
   output out = temp(drop = _:) sum=;
run;

data want(drop = type amount);
   set temp;
   by ID month;

   length con_type_amount $200;
   con_type_amount = catx(',', con_type_amount, cats('(', type, '-', amount, ')')); 

   if last.month then do;
      output;
      call missing(con_type_amount);
   end;

   retain con_type_amount;
run;

Result:

ID     month  con_type_amount      
11111  2201   (712-4000),(987-2000)
11111  2202   (712-5000)           
22222  2201   (712-4300),(987-1500)

Re: group by with concatenate over rows

Ronein — Thu, 04 Aug 2022 21:29:19 GMT

One question please:
What is the reason to use " call missing(con_Degem_amount);" ?