how to clean this data

[xiangpang]

hello,

I have some data need to clean up. In general, value in var1 should be same as value of next available day in var2. This part is easy, I could modify it with LAG function.

If the first letter in var1a or var2a is 'm' then the first letter in var1b or var2b should be 'value'  while If the first letter in var1a or var2a is 'n' then the first letter in var1b or var2b should be 'change' .

However, the data is dirty. Like ID6, some data is missing. the data in day5 of var2 is upside-down. how could I get the data as 'want'?

Thanks

data Fitness1;

input ID $Var1 $Var1a $var1b $Var2 $Var2a $var2b;

   datalines;

1   day1  m178  value1  

1   day2  m156 value2  day2  m178  value1  

1   day3                        day3   m153 value2   

1   day4  n176 change  day4         

1                                    day5  n176 change        

2  day1  m174 value1   

2  day2  m186 value2   day2  m174 value1

2  day3

2                                    day4       value2   

3  day1  m176 value1   

3  day2  m166 value2   day2  m176 value1   

6  day2  m168 value1   

6  day2                         day3   m168  value1  

6  day3  m174 value1   Day3   n164  change2    

6  day3  n164 change2  Day4   m174  value1  

6  day4  m174 value1   Day4   n164  change2    

6  day4  n164 change2  Day5   n164  change2    

6                                     day5   m174  value1

6  day5  m342 value3   unknown 

6                                    day6  m342  value3    

;

run;

data want;

ID  $Var2 $Var2a $var2b;

1   day2  m178 value1  

1   day3  m153 value2   

1   day4  n176 change           

1   day5  n176 change        

2   day2  m174 value1

2   day4       value2   

3   day2  m176 value1   

6  day3   m168  value1  

6  day4  m174              

6  day4  n164 change2 

6  day5   m174  value1

6  Day5   n164  change2    

6  unknown 

6  day6  m342  value3    

[VDD]

do you need to re-order the rows?

if the rows where reordered would you get the answer you are expecting.

Must all records be included or are there conditions where you are expected to drop the record.

[xiangpang]

how to reorder? could you use this data Fitness1 as an example?

thanks

[VDD]

@xiangpang However, the data is dirty. Like ID6, some data is missing. the data in day5 of var2 is upside-down. how could I get the data as 'want'?

should the record you have noted for ID6 need to be reordered because you say it is upside-down.

[xiangpang]

but only 2 of them not right order, others are in right order. 

[VDD]

for the 2 records that are out of order, should they be ordered?  If so you may have to re-order the records.

is there something about those 2 records where you might need to overlook them and reject them because they are not supported because of missing information?