Hi all!
I am incredibly new to SAS Code, and I appreciate if you can help me on below matter:
Here is the code for the data:
DATA TEMP;
INPUT @1 Ref 7.
@9 ID 5.
@14 LockerNo 12.
@28 Date ddmmyy10.
@39 Cust_Name $ 14.;
DATALINES;
4511445 1952 123456789123 08/12/2001 XYZ PRODUCTION
4545154 1952 987654321987 26/09/2001 DEF Co.
7895412 96321 456123789456 12/10/2000 ABC AGENCY
7895412 96321 123789654753 21/12/2000 ABC AGENCY
5451654 96321 125874934589 05/10/2006 ABC AGENCY
5451654 96321 123467984352 23/11/2004 ABC AGENCY
5451654 96321 785645464644 17/01/2005 ABC AGENCY
5451654 96321 156454654546 11/11/2004 GHI Enterprise
;
RUN;
proc sort data=have out=have;
by id DESCENDING date;
run;
data want;
set have;
by id;
retain final;
if first.id then
final=ref;
run;
What I want to achieve is:
Create a new table (named MASTER) for records that shares the same ID, but different CUST_NAME.
It will record the REF with the latest DATE and Cust_Name which differ from the REF with the latest DATE
The new table, MASTER will look like below:
Ref | ID | LockerNo | Date | Cust_Name | Final |
4511445 | 1952 | 123456789123 | 8/12/2001 | XYZ PRODUCTION | 4511445 |
4545154 | 1952 | 987654321987 | 26/9/2001 | DEF Co. | 4511445 |
5451654 | 96321 | 125874934589 | 5/10/2006 | ABC AGENCY | 5451654 |
5451654 | 96321 | 156454654546 | 11/11/2004 | GHI Enterprise | 5451654 |
I am using SAS Enterprise Guide 7.1
Appreciate your help on this!
proc sort data=temp;
by id Cust_Name date ;
run;
data want;
set temp;
by id Cust_Name date ;
if last.cust_name;
run;
proc sort data=temp;
by id Cust_Name date ;
run;
data want;
set temp;
by id Cust_Name date ;
if last.cust_name;
run;
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.