Okay what happened here? There was a response from you that showed a table in which 0202 was changed to 0101, and now it is gone. Did you do that or did the SAS Communities do that?
Nope wasn't me. let me just update it
original
date | first_id | second_id | expenses |
jul2020 | 400 | 0101 | 1000 |
jul2020 | 500 | 89 | 2001 |
aug2020 | 400 | 0202 | 1500 |
aug2020 | 500 | 89 | 2001 |
sep2020 | 402 | 0101 | 3200 |
sep2020 | 500 | 89 | 2001 |
oct2020 | 403 | 101 | 200 |
oct2020 | 500 | 89 | 2001 |
nov2020 | 400 | 202 | 100 |
nov2020 | 500 | 89 | 2001 |
the target table
date | first_id | second_id | expenses |
jul2020 | 400 | 0101 | 1000 |
jul2020 | 500 | 89 | 2001 |
aug2020 | 400 | 0101 | 1500 |
aug2020 | 500 | 89 | 2001 |
sep2020 | 400 | 0101 | 3200 |
sep2020 | 400 | 89 | 2001 |
oct2020 | 400 | 0101 | 200 |
oct2020 | 500 | 89 | 2001 |
nov2020 | 400 | 0101 | 100 |
nov2020 | 500 | 89 | 2001 |
how about this:
data have;
input date :monyy7. first_id $ second_id $ expenses;
format date monyy7.;
cards;
jul2020 400 0101 1000
jul2020 500 89 2001
aug2020 400 0202 1500
aug2020 500 89 2001
sep2020 402 0101 3200
sep2020 500 89 2001
oct2020 403 0101 200
oct2020 500 89 2001
nov2020 400 0202 100
nov2020 500 89 2001
;
proc sort data=have;
by first_id date;
run;
data want;
set have;
by first_id;
retain second_id1;
if first.first_id then second_id1=second_id;
drop second_id;
run;
proc sort data=want;
by date first_id;
run;
although I'm still not sure about your question about sums, what sums do you want from this table? Please show me. (Also, why does second_id even matter?)
It's finally time to hack! Remember to visit the SAS Hacker's Hub regularly for news and updates.
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.
Ready to level-up your skills? Choose your own adventure.