Create new table with same records on ONE variable, but different with the other

AieuYuhara — Tue, 25 Jul 2017 03:33:57 GMT

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!

Re: Create new table with same records on ONE variable, but different with the other

Jagadishkatam — Tue, 25 Jul 2017 03:58:28 GMT

proc sort data=temp;
by id Cust_Name date ;
run;

data want;
set temp;
by id Cust_Name date ;
if last.cust_name;
run;

Re: Create new table with same records on ONE variable, but different with the other

AieuYuhara — Tue, 25 Jul 2017 06:18:54 GMT

It works!!! Thanks Jag!

topic Create new table with same records on ONE variable, but different with the other in SAS Programming

Create new table with same records on ONE variable, but different with the other

Re: Create new table with same records on ONE variable, but different with the other

Re: Create new table with same records on ONE variable, but different with the other