topic Re: concatenate through group by using SAS or PROC SQL, urgent help needed in SAS Procedures

concatenate through group by using SAS or PROC SQL, urgent help needed

tapas_16880 — Thu, 19 Jul 2012 13:18:48 GMT

Hi All, I am stuck with a SAS file and not sure how to progress.

The layout is -

Cust No	Prod	Acc flag	Bal
111	a1	qaz	10
111	b1	wsx	11
222	c1	wsx	233
333	d1	grw	251
444	e1	dc	25
444	f1	vefw	1231
444	g1	wefr	36
555	h1	bdfgh	615
666	i1	qe	767
777	j1	erg	31
777	k1	eth	472
777	l1	ret	251
777	m1	dfwef	3761
888	p1	ffef	36

I want to introduce two columns in this sas file. One lets say prod_con and other can be Acc_con.

It will look for the cust_no values in this above file. If it finds distinct values then it will simply copy the values of the column Prod and Acc Flag to the new columns prod_con and Acc_con respectively. But whenever it will find duplicates, it should concatenate the values in column Prod and Acc Flag and then copy them into the new columns prod_con and Acc_con respectively.

E.g. for Cust_no = 777, it should put j1k1l1m1 to prod_con and ergethretdfwef to Acc_con and this value will be repeated off course for all 777.

Re: concatenate through group by using SAS or PROC SQL, urgent help needed

kevin_123 — Thu, 19 Jul 2012 13:48:07 GMT

Hi Tapas. I recommend doing two passes if you do know how many duplicates you can encounter. First to create your string and the second pass to add it back to the original records

data inds; 
input   custno prod $ acc_flag $ bal ; 
 datalines; 111 a1 qaz 10 
 111 b1 wsx 11 
 222 c1 wsx 233 
 333 d1 grw 251 
 444 e1 dc 25 
 444 f1 vefw 1231 
 444 g1 wefr 36 
 555 h1 bdfgh 615 
 666 i1 qe 767 
 777 j1 erg 31 
 777 k1 eth 472 
 777 l1 ret 251 
 777 m1 dfwef 
 3761 
 888 p1 ffef 36 ; 
 run; 
 proc sort data=inds; by custno prod; run; 
 data tmp_ds (keep=custno prod_con acc_con); 
 set inds; 
 by custno; 
 length prod_con acc_con $50.; 
 retain prod_con acc_con; 
 if first.custno then do;
 prod_con =""; Acc_con = ""; 
 end; 
 prod_con = trim(prod_con) || trim(prod);
 acc_con = trim(acc_con) || trim(acc_flag);
 if last.custno then output; 
 run; 
 data final_ds; 
 merge inds  
   tmp_ds;
 by custno; 
 run;

Re: concatenate through group by using SAS or PROC SQL, urgent help needed

PaigeMiller — Thu, 19 Jul 2012 13:48:45 GMT

Here's how I would approach this problem

Sort by CustNo
Proc Transpose variable Prod using BY variable CustNo
Proc Transpose variable AccFlag using BY variable CustNo
merge outputs of above 2 steps BY variable CustNo
concatenate as appropriate to produce the two new columns

Re: concatenate through group by using SAS or PROC SQL, urgent help needed

Keith — Thu, 19 Jul 2012 14:02:02 GMT

@kevin_123,

That's pretty much how I would do it. One simplification you could make is to use the CALL CATS function in place of

Varname=TRIM(....) || TRIM(...).

So the 2 lines would be :

call cats(prod_con,prod);

call cats(acc_con,acc_flag);

Re: concatenate through group by using SAS or PROC SQL, urgent help needed

Haikuo — Thu, 19 Jul 2012 14:39:33 GMT

Hi, From what I can see it is a typical 2XDOW scenario, if the group variable 'custno' is clustered, then no need to sort:

data have;

input (custno prod acc_flag) (:$) bal ;

datalines;

111 a1 qaz 10

111 b1 wsx 11

222 c1 wsx 233

333 d1 grw 251

444 e1 dc 25

444 f1 vefw 1231

444 g1 wefr 36

555 h1 bdfgh 615

666 i1 qe 767

777 j1 erg 31

777 k1 eth 472

777 l1 ret 251

777 m1 dfwef 3761

888 p1 ffef 36

;

data want;

do until (last.custno);

set have;

by custno notsorted;

length prod_con acc_con $50.;

prod_con=cats(prod_con,prod);

acc_con=cats(acc_con,acc_flag);

end;

do until (last.custno);

set have;

by custno notsorted;

output;

end;

run;

proc print;run;

Haikuo

Re: concatenate through group by using SAS or PROC SQL, urgent help needed

Haikuo — Thu, 19 Jul 2012 15:06:18 GMT

I am also wondering if there is way to use cats() +group by in proc sql in a summary function kind of way, seems neat to me.

For this question, there is another data step way, originally from Mike Zdeb (I think):

data have;

input (custno prod acc_flag) (:$) bal ;

datalines;

111 a1 qaz 10

111 b1 wsx 11

222 c1 wsx 233

333 d1 grw 251

444 e1 dc 25

444 f1 vefw 1231

444 g1 wefr 36

555 h1 bdfgh 615

666 i1 qe 767

777 j1 erg 31

777 k1 eth 472

777 l1 ret 251

777 m1 dfwef 3761

888 p1 ffef 36

;

data want;

set have (in=up) have;

by custno;

length prod_con acc_con $50.;

retain prod_con acc_con;

if first.custno then call missing (prod_con,acc_con);

if up then do;

prod_con=cats(prod_con,prod);

acc_con=cats(acc_con,acc_flag);

end;

else output;

run;

proc print;run;

Haikuo

Re: concatenate through group by using SAS or PROC SQL, urgent help needed

Astounding — Thu, 19 Jul 2012 16:23:57 GMT

Questions, questions, questions ...

For a single customer, could you ever have two observations with the same Product? With the same Account flag?

The answers so far might be assuming uniqueness. That might be correct, but the question should at least be asked.

If duplicates are possible, should the same Product appear twice in the new prod_con variable?

The programming won't change drastically ... it's just a matter of tweaking it properly.

Possibly related: Could a product or an account flag ever contain an embedded blank?

Re: concatenate through group by using SAS or PROC SQL, urgent help needed

PGStats — Thu, 19 Jul 2012 17:57:45 GMT

Hi Haikuo Bian, combining your two proposals might be optimal :

data want;

do until (last.custno);

set have (in=up) have;

by custno; /* NOTSORTED cannot be used here */

length prod_con acc_con $50.;

if up then do;

prod_con = cats(prod_con, prod);

acc_con = cats(acc_con, acc_flag);

end;

else output;

end;

run;

notsorted removed by PG.

Re: concatenate through group by using SAS or PROC SQL, urgent help needed

Haikuo — Thu, 19 Jul 2012 21:09:58 GMT

Yes, PG. That is a good combination, Thanks!

Haikuo

Re: concatenate through group by using SAS or PROC SQL, urgent help needed

ErikT — Fri, 20 Jul 2012 05:05:35 GMT

How about this:

Create an index on Cust_no on the file

Then create an extra file of unique customers numbers

Read the file with the unique customers numbers and for each one go to the original file using the KEY option in the SET statement to retrieve and concatenate the Prod and Acc_flag variables. The _iorc_=0 and _error_=0 at the end of the step are to prevent error messages in the log.

data original(index=(Cust_no));

input Cust_no Prod $ ACC_flag $ Bal $ ;

datalines;

111 a1 qaz 10

111 b1 wsx 11

222 c1 wsx 233

333 d1 grw 251

444 e1 dc 25

444 f1 vefw 1231

444 g1 wefr 36

555 h1 bdfgh 615

666 i1 qe 767

777 j1 erg 31

777 k1 eth 472

777 l1 ret 251

777 m1 dfwef 3761

888 p1 ffef 36

run;

proc sort data=original(keep=cust_no) out=customers nodupkey;

by Cust_no;

run;

data new;

set customers;

length Prod_con Acc_con $50;

do until (_iorc_ ne 0);

set original key = Cust_no;

if _iorc_ = 0 then do;

Prod_con = CATX('|',Prod_Con,Prod);

Acc_con = CATX('|',Acc_con,Acc_flag);

end;

output;

_iorc_ = 0; _error_=0;

run;

proc print;

run;

Re: concatenate through group by using SAS or PROC SQL, urgent help needed

flyerrhappy2015 — Tue, 19 May 2015 23:07:27 GMT

Is there any way to do this in proc sql?

I need to calcualte 40+ column for sum and mean. SQL is easier to calculate sum and mean, but how can I concatenate row values across each ID: E.g. for Cust_no = 777, it should put j1k1l1m1 to prod_con and get the total balance for each SSN in proc sql

Re: concatenate through group by using SAS or PROC SQL, urgent help needed

Ksharp — Wed, 20 May 2015 13:59:06 GMT

Yes. There is one way. But you need to list all of these unique value as columns , You wouldn't want to that . That is horrible .