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;
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
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.