BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
AieuYuhara
Fluorite | Level 6

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
1 ACCEPTED SOLUTION

Accepted Solutions
Patrick
Opal | Level 21

@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

7 REPLIES 7
PeterClemmensen
Tourmaline | Level 20

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
Fluorite | Level 6
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
Fluorite | Level 6
 

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

Patrick
Opal | Level 21

@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
Fluorite | Level 6
It works!!!! Thanks a lot!
AieuYuhara
Fluorite | Level 6

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 !

PeterClemmensen
Tourmaline | Level 20

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;

sas-innovate-2024.png

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.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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