compare cell entries and return values In SAS

Question

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   

Answer 1

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