variable counting

[capam]

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

[Kurt_Bremser]

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.

[capam]

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

[capam]

Kurt,

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

[PBsas · Accepted solution]

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

[PBsas]

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

[Kurt_Bremser]

---

@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.

[PBsas]

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;

[viditmalhotra89]

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;