topic Re: how to clean this data in SAS Programming

how to clean this data

xiangpang — Sat, 15 Sep 2018 01:40:50 GMT

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

Re: how to clean this data

VDD — Sat, 15 Sep 2018 13:01:47 GMT

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.

Re: how to clean this data

xiangpang — Sat, 15 Sep 2018 14:00:13 GMT

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

thanks

Re: how to clean this data

VDD — Sat, 15 Sep 2018 14:08:04 GMT

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

Re: how to clean this data

xiangpang — Sat, 15 Sep 2018 16:08:55 GMT

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

Re: how to clean this data

VDD — Sun, 16 Sep 2018 14:32:00 GMT

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?