Data Cleansing - Data Mapping

[AieuYuhara]

Hi all,

I am new in SAS and got problem for data mapping. 

As you can see on the attachment, I am trying to create a new variable that refers to a column for Ref. 

Same ID might have different Ref

So to standardize, I am going to make new variable, called Final to select the Ref with latest Date

Appreciate your help on this. 

I am using SAS Enterprise Guide 7.1

[PeterClemmensen]

This task is pretty straight forward in SAS, but please post your data in the form of a data step to get a code answer 🙂

Not many users in here want to download attached files

[AieuYuhara]

Ok noted. Thank you for letting me know.

I already post the data in the form of data step.
Appreciate your help on this!

[AieuYuhara]

Hi all,

I am new in SAS and got problem for data mapping. 

As you can see below, I am trying to create a new variable that refers to a column for Ref. 

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 ABC AGENCY    
;
RUN; 

Same ID might have different Ref

So to standardize, I am going to make new variable, called Final to select the Ref with latest Date

Variable Final cannot be empty and it will be fill respectively to the Ref with the latest Date:

Here is the example of table I am trying to produce in SAS:

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
7895412	96321	456123789456	12/10/2000	ABC AGENCY	5451654
7895412	96321	123789654753	21/12/2000	ABC AGENCY	5451654
5451654	96321	125874934589	5/10/2006	ABC AGENCY	5451654
5451654	96321	123467984352	23/11/2004	ABC AGENCY	5451654
5451654	96321	785645464644	17/1/2005	ABC AGENCY	5451654
5451654	96321	156454654546	11/11/2004	ABC AGENCY	5451654

Appreciate your help on this. 

I am using SAS Enterprise Guide 7.1

[AieuYuhara]

Hi all,

I am new in SAS and got problem for data mapping. 

As you can see below, I am trying to create a new variable that refers to a column for Ref. 

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 ABC AGENCY    
;
RUN; 

Same ID might have different Ref

So to standardize, I am going to make new variable, called Final to select the Ref with latest Date

Variable Final cannot be empty and it will be fill respectively to the Ref with the latest Date:

Here is the example of table I am trying to produce in SAS:

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
7895412	96321	456123789456	12/10/2000	ABC AGENCY	5451654
7895412	96321	123789654753	21/12/2000	ABC AGENCY	5451654
5451654	96321	125874934589	5/10/2006	ABC AGENCY	5451654
5451654	96321	123467984352	23/11/2004	ABC AGENCY	5451654
5451654	96321	785645464644	17/1/2005	ABC AGENCY	5451654
5451654	96321	156454654546	11/11/2004	ABC AGENCY	5451654

Appreciate your help on this. 

I am using SAS Enterprise Guide 7.1

[Patrick · Accepted solution]

@AieuYuhara

Something like below should work:

DATA have;
  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 ABC AGENCY    
;
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;

[AieuYuhara]

It works!!!! Thanks a lot!

[AieuYuhara]

Below is the SAS 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 ABC AGENCY    
;
RUN; 

I am trying to achieve this below table by using SAS:

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
7895412	96321	456123789456	12/10/2000	ABC AGENCY	5451654
7895412	96321	123789654753	21/12/2000	ABC AGENCY	5451654
5451654	96321	125874934589	5/10/2006	ABC AGENCY	5451654
5451654	96321	123467984352	23/11/2004	ABC AGENCY	5451654
5451654	96321	785645464644	17/1/2005	ABC AGENCY	5451654
5451654	96321	156454654546	11/11/2004	ABC AGENCY	5451654

Thank You! Appreciate your help on this !

[PeterClemmensen]

NOT TESTED:

proc sql;
	create table want as
	select *
		  ,a.Ref as final
	from TEMP, (select Ref, ID from TEMP group by ID having date = max(date)) as a
	where TEMP.ID=a.ID;
quit;