I have an input data like this
Column1 | Column2 | Column3 |
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 |
i am looking for an output like
Column1 | Column2 | Column3 |
1 | AB-LP | 14/03/2018-25/09/1901 |
2 | DC-JP | 23/07/2002 -06/12/1925 |
3 | NP-QR | 13/11/1910-03/04/1974 |
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.
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;
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.