BookmarkSubscribeRSS Feed
skallamp
Obsidian | Level 7

I would like to replace missing values of score based on the ID and date.

Below is the example. You will notice that I have replace ID 3001, 201601 with 3.7.

How can we achieve this with retain or lag statement? Is there any other technique?

 

Before

 

ID mo_id Score
1001 201601 3.5
1001 201602 3.6
1001 201603  
1001 201604 3.7
1001 201605 3.2
1001 201606  
1001 201607  
1001 201608 3.4
2001 201601 3.5
2001 201602  
2001 201603 3.6
2001 201604 3.7
2001 201605 3.2
2001 201606  
2001 201607  
2001 201608 3.6
3001 201601  
3001 201602 3.7
3001 201603  
3001 201604 3.7
3001 201605 3.2
3001 201606  
3001 201607 3.9
3001 201608  
4001 201601  
3001 201602 3.6
3001 201603  
3001 201604 3.7
3001 201605 3.2
3001 201606  
3001 201607  
3001 201608  

 

Retain

 

ID mo_id Score
1001 201601 3.5
1001 201602 3.6
1001 201603 3.6
1001 201604 3.7
1001 201605 3.2
1001 201606 3.2
1001 201607 3.2
1001 201608 3.4
2001 201601 3.5
2001 201602 3.5
2001 201603 3.6
2001 201604 3.7
2001 201605 3.2
2001 201606 3.2
2001 201607 3.2
2001 201608 3.6
3001 201601 3.7
3001 201602 3.7
3001 201603 3.7
3001 201604 3.7
3001 201605 3.2
3001 201606 3.2
3001 201607 3.9
3001 201608 3.9
4001 201601 3.6
3001 201602 3.6
3001 201603 3.6
3001 201604 3.7
3001 201605 3.2
3001 201606 3.2
3001 201607 3.2
3001 201608 3.2

 

4 REPLIES 4
LinusH
Tourmaline | Level 20
Retain would work but requires that you sort your data descending by year-month.
Data never sleeps
skallamp
Obsidian | Level 7
for some reason its not working....


##- Please type your reply above this line. Simple formatting, no
attachments. -##
BrunoMueller
SAS Super FREQ

Hi

 

You could use the SUM statement, this will retain the value automatically and ignore expressions that result in missing (.)

 

data have;
  infile cards missover;
  seqNr = _n_;
  input
    ID
    mo_id
    Score
  ;
cards;
1001  201601  3.5
1001  201602  3.6
1001  201603   
1001  201604  3.7
1001  201605  3.2
1001  201606   
1001  201607   
1001  201608  3.4
2001  201601  3.5
2001  201602   
2001  201603  3.6
2001  201604  3.7
2001  201605  3.2
2001  201606   
2001  201607   
2001  201608  3.6
3001  201601   
3001  201602  3.7
3001  201603   
3001  201604  3.7
3001  201605  3.2
3001  201606   
3001  201607  3.9
3001  201608   
4001  201601   
3001  201602  3.6
3001  201603   
3001  201604  3.7
3001  201605  3.2
3001  201606   
3001  201607   
3001  201608   
;

data want;
  set have;
  score2 + (-score2 + score);
run;

Bruno

Ksharp
Super User

data have;
  infile cards missover;
  n = _n_;
  input
    ID
    mo_id
    Score
  ;
cards;
1001  201601  3.5
1001  201602  3.6
1001  201603   
1001  201604  3.7
1001  201605  3.2
1001  201606   
1001  201607   
1001  201608  3.4
2001  201601  3.5
2001  201602   
2001  201603  3.6
2001  201604  3.7
2001  201605  3.2
2001  201606   
2001  201607   
2001  201608  3.6
3001  201601   
3001  201602  3.7
3001  201603   
3001  201604  3.7
3001  201605  3.2
3001  201606   
3001  201607  3.9
3001  201608   
4001  201601   
3001  201602  3.6
3001  201603   
3001  201604  3.7
3001  201605  3.2
3001  201606   
3001  201607   
3001  201608   
;

data temp;
  set have;
  by id notsorted ;
  retain x;
  if first.id then call missing(x);
  if not missing(score) then x=score;
run;
proc sort data=temp;by descending n;run;
data want;
  set temp;
  by id notsorted ;
  retain new_score;
  if first.id then call missing(new_score);
  if not missing(x) then new_score=x;
  drop x;
run;
proc sort data=want;by n;run;


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
  • 4 replies
  • 1152 views
  • 0 likes
  • 4 in conversation