## variable counting

Solved
Frequent Contributor
Posts: 77

# variable counting

I tried the following code without success. Fault_Count is a new table and All_Units_Results is an existing table.

``````data Fault_Count ;
set All_Units_Results(keep= fault_code);
if first.fault_code then fault_count=0;
fault_count=fault_count+1;
*if last.fault_code then output;

run;``````

An example of Fault_Count follows. I'm trying to get a count of each individual fault occurance.

20-1802
20-1803
20-1805
20-1803
20-1803
20-1802
20-1801

The output should look something like:

20-1801     1

20-1802     2

20-1803     3

20-1805     1

The above code produces:

20-1802   .
20-1803   .
20-1805   .
20-1803   .
20-1803   .
20-1802   .

capam

Accepted Solutions
Solution
‎08-11-2017 02:53 PM
Contributor
Posts: 30

## Re: variable counting

proc sql;
create table fault_count as
select fault_code, count(*) as fault_count
from all_units_results
group by fault_code;
quit;

All Replies
Super User
Posts: 10,278

## Re: variable counting

``````proc sql;
create table fault_count as
select fault_code, count(*)
from all_units_results
group by fault_count;
quit;``````

In your code, you missed a retain statement for variable fault_count. Alternatively, you could use

``fault_count + 1;``

as increment statement. SAS will then implicitly retain the variable.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Frequent Contributor
Posts: 77

## Re: variable counting

Thanks Kurt,

I tried the following code - notice group by is corrected.

``````proc sql;
create table fault_count as
select fault_code, count(*)
from all_units_results
group by fault_code;
quit;``````

It works well. Thank you very much.

capam

Frequent Contributor
Posts: 77

## Re: variable counting

Kurt,

How do you give the output a label. Currently it comes back as _TEMG001. Thanks.
Solution
‎08-11-2017 02:53 PM
Contributor
Posts: 30

## Re: variable counting

proc sql;
create table fault_count as
select fault_code, count(*) as fault_count
from all_units_results
group by fault_code;
quit;

Contributor
Posts: 30

## Re: variable counting

Please change the soultion to the Kurt's post. He was the first to solve your question.

Super User
Posts: 10,278

## Re: variable counting

capam wrote:
Kurt,

How do you give the output a label. Currently it comes back as _TEMG001. Thanks.

See @PBsas's suggestion. And start working through the documentation for proc sql. SQL is the "swiss army knife" for databases, just as the data step is for SAS datasets.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Contributor
Posts: 30

## Re: variable counting

``````data have;
input fault_code \$;
cards;
20-1802
20-1803
20-1805
20-1803
20-1803
20-1802
20-1801
;
run;

proc sort data=have; by fault_code; run;

data want;
set have;
by fault_code;
if first.fault_code then fault_count=1;
else fault_count+1;
if last.fault_code;
run;``````
User
Posts: 1

## Re: variable counting

Apart from PROC SQL, if you wish to perform the action using DATA STEP, you may proceed like this:

data All_Units_Results;
input fault_code\$;
datalines;
20-1802
20-1803
20-1805
20-1803
20-1803
20-1802
20-1801
;
proc sort data=all_units_results;

by fault_code;

data Fault_Count;
set All_Units_Results;

by fault_code;
if first.fault_code then fault_count=0;
fault_count+1;
if last.fault_code then output;

run;

☑ This topic is solved.

Discussion stats
• 8 replies
• 384 views
• 2 likes
• 4 in conversation