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
