How to count by variable

[Yiting]

I have a table like the first 3 columns of this and want to calculate for the 4th column which is a count of A appearing in each of the first 3 columns. how can I code that? Thanks in advance.

(I probably simplified the question. In reality, both the variable name and values are very long... I have around 30 variables and each value is like 40 letters long. Plus there are other variables in  the table. That probably gave the error message as I mentioned below.)

Var	Var2	Var3	Var_A
A	B	C	1
B	A	A	2
c	B	C	0
A	A	A	3
B	B	C	0
c	A	A	2
A	B	C	1
B	A	A	2
c	B	C	0

[WarrenKuhfeld]

Var_A = (Var='A')+(Var2='A')+(Var3='A');

Use an array and a DO loop if you have a bunch of variables.

[Yiting]

I got error message...ERROR: Numeric expression requires a numeric format.

[singhsahab]

data test;
length a b c $1.;
input a $ b  c ;
datalines;
a b c
a a c
a a a
c c c
a a a
;
run;

proc sql;
create table want as 
select a,b,c,sum(a1,a2,a3) as var_a from(
select a,b,c ,input(ifc(a eq 'a','1','0'),1.) as a1,
input(ifc(b eq 'a','1','0'),1.) as a2,
input(ifc(c eq 'a','1','0'),1.)as a3 from test);
quit;

[KachiM]

Try this.

data test;
length a b c $1.;
input a $ b  c ;
datalines;
a b c
a a c
a a a
c c c
a a a
;
run;

data want;
   set test; 
   count = count(catt(a,b,c),'a');
run;

[Yiting]

thanks... got error message

ERROR: The decimal specification of 3 must be less than the width specification of 2.

[KachiM]

Can you post your input data set with some 10 observations. The best will be the observation you think that results in ERROR.

[Yiting]

hmm.. it is a super wide table with many variables...i will not be able to post the whole width.

what in the variables or their contents might have caused the problem?

What annoys me most is that in my syntax, I dealt with variables, not decimals... 😞

[singhsahab]

data want;
set test;
new_var=catx(',',a,b,c);
var_a = count(new_var,"a");
drop new_var;
run;

[Yiting]

Thanks. same as above i got error message

ERROR: The decimal specification of 3 must be less than the width specification of 2.

I guess its related to that in my real dataset, i have more variables than indicated here?

[ballardw]

---

@Yiting wrote:

Thanks. same as above i got error message

ERROR: The decimal specification of 3 must be less than the width specification of 2.

I guess its related to that in my real dataset, i have more variables than indicated here?

---

Show your data and the exact code you are running.

None of the example code suggested would generate that sort of error. Most likely you have a FORMAT statement with a bad format.

1    data dummy;
2       x=23;
3       format x 2.3;
                 ---
                 156
ERROR 156-185: The decimal specification of 3 must be less than the width specification of 2.

4    run;

If you want to display 3 decimals then the format actually needs to have a width of at least 4 to allow the decimal plus 3 characters.

If you want to display a value such as 12.345 the format would be 6.3. The first number is the TOTAL length and the part after the decimal is how many digits to display to the right of the decimal.

BTW, when posting any error message post the entire data step or procedure along with the error messages. Sometimes the error comes because of something incorrect before the actual displayed error. Best is to copy the text from the log and then paste into a code box opened with the forum's {I} or running man icon.

Note the example I post shows ___ under the 2.3. That indicates the specific location of the error. The main message windows on this forum will reformat the text so that is not quite a clear:

1 data dummy;

2 x=23;

3 format x 2.3;

---

156

ERROR 156-185: The decimal specification of 3 must be less than the width specification of 2.

4 run;