Read different value in excel and return value

Reply
Contributor
Posts: 25

Read different value in excel and return value

[ Edited ]

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   

 first I need compare each A value to your cutoff of 30. Then perhaps i want to index number of the first A value that is > 30? And then compare the min of that over every row?

PROC Star
Posts: 7,537

Re: Read different value in excel and return value

You would have to explain where your values numbers come from. Why do customers 1 and 2 have values of 1, while customer 3 has a value of 3.

 

Art, CEO, AnalystFinder.com

 

Contributor
Posts: 25

Re: Read different value in excel and return value

Thank you so much for you comment:

for costumer 1, in the two raw there is a value bigger than >30. Then select the min number of cells which is in the first row, that have A4 only. that is why its 1 .

for customer 2 : there is only one value >30 . and only one value which is A4.

for customer 3 : there is one value > 30. and there is three values before it which are A2, A3 and A4.
PROC Star
Posts: 7,537

Re: Read different value in excel and return value

If there is a maximum of one a1 to a4 value that can be greater than 30, then the following should work:

data have;
  input customer_NO  id   A1-A4;
  cards; 
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
;

data want (keep=customer_NO count_ac_num values);
  set have;
  by customer_NO;
  if first.customer_NO then do;
    count_ac_num=0;
    values=4;
  end;
  retain values;
  array a_array(*) a1-a4;
  do i=1 to dim(a_array);
    if a_array(i) gt 30 then do;
      count_ac_num+1;
      values=min(values,dim(a_array)-i);
      leave;
    end;
    else if i eq dim(a_array) then values=min(values,dim(a_array));
  end;
  if last.customer_NO then output;
run;

Otherwise, you'll have to specify how you'd want to treat VALUES in such cases.

 

Art, CEO, AnalystFinder.com

Contributor
Posts: 25

Re: Read different value in excel and return value

##-

69 data want4 (keep=customer_NO count_ac_num values);
70 set WORK.test;
71 by customer_NO;
72 if first.customer_NO then do;
73 count_ac_num=0;
74 values=4;
75 end;
76 retain values;
77 array a_array(*) a1-a4;
78 do i=1 to dim(a_array);
79 if a_array(i) gt 30 then do;
80 count_ac_num+1;
81 values=min(values,dim(a_array)-i);
82 leave;
83 end;
84 else if i eq dim(a_array) then values=min(values,dim(a_array));
85 end;
86 if last.customer_NO then output;
87 run;
ERROR: BY variables are not properly sorted on data set WORK.TEST.
customer_no=3 acct_type=10 a1=0 a2=30 a3=0 a4=0 FIRST.customer_no=0 LAST.customer_no=0 count_ac_num=0 values=4 i=. _ERROR_=1 _N_=8
NOTE: The SAS System stopped processing this step because of errors.
NOTE: There were 9 observations read from the data set WORK.TEST.
-##
Super Contributor
Posts: 349

Re: Read different value in excel and return value

Read the log, the error message has an obvious solution.
Contributor
Posts: 25

Re: Read different value in excel and return value

Iam still can't figure it out.
Super User
Posts: 10,123

Re: Read different value in excel and return value

Assuming I understand what you are talking about.

 

data have;
  input customer_NO  id   A1-A4;
  cards; 
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
;

proc sql;
select customer_NO,
sum(A1>30) as a1,
sum(A2>30) as a2,
sum(A3>30) as a3,
sum(A4>30) as a4,
calculated a1+calculated a2+calculated a3+calculated a4 as count_ac_num ,
case when 
calculated a1=0 and calculated a2=0 and calculated a3=0 and calculated a4=0 then 4
when
calculated a2=0 and calculated a3=0 and calculated a4=0 then 3
when
calculated a3=0 and calculated a4=0 then 2
when
calculated a4=0 then 1
else 0 end as values   
 from have
  group by customer_NO;
quit;
Ask a Question
Discussion stats
  • 7 replies
  • 129 views
  • 0 likes
  • 4 in conversation