DATA Step, Macro, Functions and more

more value in same cell based on one column

Reply
Contributor
Posts: 21

more value in same cell based on one column

I have an input data like this

 

Column1Column2Column3
1AB14/03/2018
2DC23/07/2002
3NP13/11/1910
1LP25/09/1901
2JP06/12/1925
3QR03/04/1974

 

 

i am looking for an output like

 

Column1Column2Column3
1AB-LP14/03/2018-25/09/1901
2DC-JP23/07/2002 -06/12/1925
3NP-QR13/11/1910-03/04/1974
Super User
Posts: 23,963

Re: more value in same cell based on one column

Posted in reply to Deepankar

Is it always only 2 per record?

 

1. Sort data (PROC SORT)

2. Use BY/LAST to identify LAST records

3. Use LAG() to capture previous value

4. Use CATX()

 

Or Transpose and then use CATX on the output to combine the variables.

 

 

Super User
Posts: 2,048

Re: more value in same cell based on one column

Posted in reply to Deepankar
data have;
input (Column1	Column2	Column3) (:$10.);
datalines;
1	AB	14/03/2018
2	DC	23/07/2002
3	NP	13/11/1910
1	LP	25/09/1901
2	JP	06/12/1925
3	QR	03/04/1974
;


data _null_;
length _Column2 _Column3 $100;
if _n_=1 then do;
   dcl hash H (ordered: "A") ;
   h.definekey  ("Column1") ;
   h.definedata ("Column1","_Column2", "_Column3") ;
   h.definedone () ;
   call missing(of _all_);
   end;
set have end=l;
if h.find() ne 0 then h.replace(key:column1,data:Column1,data:Column2,data:Column3);
else do;_Column2=catx('-',_Column2,Column2);
_Column3=catx('-',_Column3,Column3);
h.replace();
end;
if l then h.output(dataset:'want');
run;
Super User
Posts: 10,848

Re: more value in same cell based on one column

Posted in reply to Deepankar

data have;
input (Column1	Column2	Column3) (:$10.);
datalines;
1	AB	14/03/2018
2	DC	23/07/2002
3	NP	13/11/1910
1	LP	25/09/1901
2	JP	06/12/1925
3	QR	03/04/1974
;
proc sort data=have;
by column1;
run;
data want;
length new2 new3 $ 200;
do until(last.column1);
 set have;
 by column1;
 new2=catx('-',new2,column2);
 new3=catx('-',new3,column3);
end;
drop column2 column3;
run;

Ask a Question
Discussion stats
  • 3 replies
  • 96 views
  • 0 likes
  • 4 in conversation