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: 8,165

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: 8,165

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.
-##
Valued Guide
Posts: 580

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,784

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;``````
Discussion stats
• 7 replies
• 157 views
• 0 likes
• 4 in conversation