DATA Step, Macro, Functions and more

Data Cleansing - Data Mapping

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 16
Accepted Solution

Data Cleansing - Data Mapping

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


Data_Mapping.PNG

Accepted Solutions
Solution
‎07-24-2017 08:56 PM
Respected Advisor
Posts: 4,173

Re: How do I do Data Cleansing - Data Mapping

Posted in reply to AieuYuhara

@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;

View solution in original post


All Replies
PROC Star
Posts: 754

Re: Data Cleansing - Data Mapping

Posted in reply to AieuYuhara

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 Smiley Happy

 

Not many users in here want to download attached files

Occasional Contributor
Posts: 16

Re: Data Cleansing - Data Mapping

Ok noted. Thank you for letting me know.

I already post the data in the form of data step.
Appreciate your help on this!
Occasional Contributor
Posts: 16

How do I do Data Cleansing - Data Mapping

Posted in reply to 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:

RefIDLockerNoDateCust_NameFinal
451144519521234567891238/12/2001XYZ PRODUCTION4511445
4545154195298765432198726/9/2001DEF Co.4511445
78954129632145612378945612/10/2000ABC AGENCY5451654
78954129632112378965475321/12/2000ABC AGENCY5451654
5451654963211258749345895/10/2006ABC AGENCY5451654
54516549632112346798435223/11/2004ABC AGENCY5451654
54516549632178564546464417/1/2005ABC AGENCY5451654
54516549632115645465454611/11/2004ABC AGENCY5451654

 

Appreciate your help on this. 

I am using SAS Enterprise Guide 7.1

Solution
‎07-24-2017 08:56 PM
Respected Advisor
Posts: 4,173

Re: How do I do Data Cleansing - Data Mapping

Posted in reply to AieuYuhara

@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;
Occasional Contributor
Posts: 16

Re: How do I do Data Cleansing - Data Mapping

It works!!!! Thanks a lot!
Occasional Contributor
Posts: 16

Re: Data Cleansing - Data Mapping

Posted in reply to 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:

RefIDLockerNoDateCust_NameFinal
451144519521234567891238/12/2001XYZ PRODUCTION4511445
4545154195298765432198726/9/2001DEF Co.4511445
78954129632145612378945612/10/2000ABC AGENCY5451654
78954129632112378965475321/12/2000ABC AGENCY5451654
5451654963211258749345895/10/2006ABC AGENCY5451654
54516549632112346798435223/11/2004ABC AGENCY5451654
54516549632178564546464417/1/2005ABC AGENCY5451654
54516549632115645465454611/11/2004ABC AGENCY5451654

 

Thank You! Appreciate your help on this !

PROC Star
Posts: 754

Re: Data Cleansing - Data Mapping

Posted in reply to AieuYuhara

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;
☑ This topic is solved.

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

Discussion stats
  • 7 replies
  • 197 views
  • 0 likes
  • 3 in conversation