BookmarkSubscribeRSS Feed
Deepankar
Calcite | Level 5

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
3 REPLIES 3
Reeza
Super User

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.

 

 

novinosrin
Tourmaline | Level 20
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;
Ksharp
Super User

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;

SAS Innovate 2025: Save the Date

 SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!

Save the date!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 3 replies
  • 927 views
  • 0 likes
  • 4 in conversation