And just adding a small fix for change_date so the value doesn't get truncated ...and also is an actual SAS Date value and not a string.
data have;
infile datalines truncover dsd;
input report_date :mmddyy. ID_PART $ PART_TYPE $ PART_NM $ PART_COST;
format report_date date9. part_cost dollar16.2;
datalines;
1/01/2022,1A1,AA,BOLT,3.00
1/02/2022,1A1,AA,BOLT,3.00
1/03/2022,1A1,AA,BOLT,4.00
1/04/2022,1A1,AA,BOLT,4.00
1/05/2022,1A1,AA,BOLT,4.00
1/06/2022,1A1,BB,BOLT,4.00
1/07/2022,1A1,BB,BOLT,4.00
1/08/2022,1A1,BB,BOLT,4.00
1/09/2022,1A1,BB,BOLT,4.00
1/10/2022,1A1,BB,BOLT,4.00
1/11/2022,1A1,BB,BOLT,4.00
1/12/2022,1A1,CC,BOLT,5.00
1/13/2022,1A1,CC,BOLT,5.00
1/14/2022,1A1,CC,BOLT,5.00
1/01/2022,1xx,AA,BOLT,3.00
1/02/2022,1xx,AA,BOLT,3.00
1/03/2022,1xx,AA,BOLT,4.00
1/10/2022,1xx,AA,BOLT,4.00
;
/* ensure table is sorted by ID_PART and REPORT_DATE */
proc sort data=have presorted;
by id_part report_date;
run;
%let varlist=;
proc sql noprint;
select name into :varlist separated by ' '
from dictionary.columns
where libname='WORK' and memname='HAVE' and upcase(name) not in ('ID_PART', 'REPORT_DATE')
;
quit;
proc transpose data=have out=inter prefix=_report_date_ ;
by id_part;
id report_date;
var &varlist;
run;
data want(keep=id_part change_date change_field previous_value changed_to);
if 0 then set inter(keep=id_part);
length change_date 8 change_field $32 previous_value changed_to $100;
format change_date date9.;
set inter(rename=(_name_=Change_Field));
by id_part;
array _vals {*} _report_date_:;
do _i=2 to dim(_vals);
if _vals[_i-1] ne _vals[_i] then
do;
change_date=input(scan(vname(_vals[_i]),-1,'_'),date9.);
previous_value=_vals[_i-1];
changed_to=_vals[_i];
output;
end;
end;
run;
proc print data=want;
run;
... View more