turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Find a Community

- Home
- /
- SAS Programming
- /
- General Programming
- /
- Read different value in excel and return value

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

10-08-2017 01:32 PM - edited 10-08-2017 02:18 PM

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?

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to ihlayyel

10-08-2017 02:11 PM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to art297

10-08-2017 02:26 PM

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.

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.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to ihlayyel

10-08-2017 03:47 PM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to art297

10-09-2017 02:18 AM

##-

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.

-##

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.

-##

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to ihlayyel

10-09-2017 03:03 AM

Read the log, the error message has an obvious solution.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to ihlayyel

10-09-2017 03:29 AM

Iam still can't figure it out.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to ihlayyel

10-09-2017 09:18 AM

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;
```