DATA Step, Macro, Functions and more

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

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 16
Accepted Solution

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

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:

RefIDLockerNoDateCust_NameFinal
451144519521234567891238/12/2001XYZ PRODUCTION4511445
4545154195298765432198726/9/2001DEF Co.4511445
5451654963211258749345895/10/2006ABC AGENCY5451654
54516549632115645465454611/11/2004GHI Enterprise5451654

 

I am using SAS Enterprise Guide 7.1 

Appreciate your help on this!


Accepted Solutions
Solution
‎07-25-2017 02:18 AM
Trusted Advisor
Posts: 1,137

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

Posted in reply to AieuYuhara
proc sort data=temp;
by id Cust_Name date ;
run;

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

View solution in original post


All Replies
Solution
‎07-25-2017 02:18 AM
Trusted Advisor
Posts: 1,137

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

Posted in reply to AieuYuhara
proc sort data=temp;
by id Cust_Name date ;
run;

data want;
set temp;
by id Cust_Name date ;
if last.cust_name;
run;
Thanks,
Jag
Occasional Contributor
Posts: 16

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

Posted in reply to Jagadishkatam
It works!!! Thanks Jag!
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 2 replies
  • 138 views
  • 1 like
  • 2 in conversation