Data set is below.
I would like to retain the Max YEAR with the corresponding FIRST_FOUND detail. I've tried a retain statement but it seems its not working. Not sure if I am able to create a MAX(YEAR) within in a data step or if i will have to create that in a proc sql prior to performing my data step. Any assistance would be appreciated.
Primary_ID | Secondary_ID | YEAR | CONDITION | STATUS | FIRST_FOUND | count |
1ABCDEFG2345 | 4567 | 2014 | 108 | NEW | IT | 0 |
1ABCDEFG2345 | 4567 | 2015 | 108 | DROPPED | 1 | |
1ABCDEFG2345 | 4567 | 2016 | 108 | DROPPED | 2 | |
1ABCDEFG2345 | 4567 | 2017 | 108 | DROPPED | 3 | |
1ABCDEFG2345 | 4567 | 2018 | 108 | DROPPED | 4 | |
1ABCDEFG2345 | 4567 | 2019 | 108 | DROPPED | 5 |
This is how i would like my data to look
Primary_ID | Secondary_ID | DOS_YEAR | CONDITION | STATUS | FIRST_FOUND | count |
1ABCDEFG2345 | 4567 | 2018 | 108 | NEW | IT | 5 |
Code Below:
data source_detail_1 (keep=Primary_ID Secondary_ID YEAR CONDITION STATUS FIRST_FOUND COUNT YEAR1 );
set source_detail (where=(PRIMARY_ID='1ABCDEFG2345')) ;
retain first_found;
YEAR1= MAX(YEAR);
run;
Solved myself
proc sort data=WORK.SOURCE_DETAIL; by primary_id year descending first_found ;run;
data temp1;
set WORK.SOURCE_DETAIL;
length first_found1 $3.;
retain first_found1 ;
by primary_id year descending first_found ;
if first.primary_id then do;
first_found1=first_found;
end;
output;
drop first_found;
run;
Solved myself
proc sort data=WORK.SOURCE_DETAIL; by primary_id year descending first_found ;run;
data temp1;
set WORK.SOURCE_DETAIL;
length first_found1 $3.;
retain first_found1 ;
by primary_id year descending first_found ;
if first.primary_id then do;
first_found1=first_found;
end;
output;
drop first_found;
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.