在SAS中比较单元格条目和返回值 [英] compare cell entries and return values In SAS

问题描述

只是想比较单元格条目和返回值.

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     

如何统计每个customer_no有(value > 030)的ID
然后，出现 030 之前的最小值数.

how to count the ID that has (value > 030) for each customer_no
then, the min number of values before 030 appears.

预期的输出类似于:

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.

假设引号没有意义(看起来有人有一个像 "xxx" 这样的字符串，其中包含写入 CSV 文件的实际引号字符，因此添加了额外的引号以保护现有的引号，所以它变成了 """xxx""") 你可以使用 compress() 函数来删除它们.

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.

然后您可以将结果字符串拆分为 3 个字符的子字符串.

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

这篇关于在SAS中比较单元格条目和返回值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！