just would like to compare cell entries and return values.
coustmer_NO id A1 A2 A3 A4
1 5 10 20 45 0
1 13 0 45 2 5
2 4 0 10 7 8
2 3 7 9 55 0
2 10 0 0 0 0
3 4 90 8 14 3
3 10 20 7 4 15
how to count the ID that has (value > 030) for each customer_no
then, the min number of values before 030 appears.
The expected output would be something like:
cosutmer_no , count_ac_num , values
1 2 1
2 1 1
3 1 3
I would recommend converting to a more vertical structure. Then you can begin trying to apply your business logic, although I am having a hard time understanding what that is.
Assuming that the quotes are not meaningful (looks like someone had a string like "xxx" that had actual quote characters in it that was written to a CSV file and so extra quotes where added to protect the existing quotes so it became """xxx""") you could just use compress() function to remove them.
You could then just split the resulting string into 3 character substrings.
data want ;
set have ;
array h history1 history2 ;
do history=1 to dim(h);
h(history)=compress(h(history),'"');
length index 8 value $3 ;
do index=1 by 1 until (value=' ');
value=substrn(h(history),3*(index-1)+1,3);
if value ne ' ' then output;
end;
end;
drop history1 history2;
run;
So you end up with something like this:
Obs id type history index value
1 1 13 1 1 STD
2 1 13 1 2 STD
3 1 13 1 3 058
4 1 13 1 4 030
5 1 13 2 1 STD
6 1 13 2 2 030
7 1 13 2 3 066
8 1 13 2 4 036
9 1 13 2 5 030
10 1 13 2 6 STD
11 1 13 2 7 STD
12 1 13 2 8 STD
13 1 13 2 9 STD
14 1 13 2 10 STD
15 1 3 1 1 STD
16 1 3 1 2 STD
17 1 3 1 3 STD
18 1 3 1 4 XXX
19 1 3 1 5 STD
20 1 3 1 6 XXX
21 1 3 1 7 S
