DATA Step, Macro, Functions and more

variable counting

Accepted Solution Solved
Reply
Contributor
Posts: 30
Accepted Solution

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
a week ago
Occasional Contributor
Posts: 19

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;

View solution in original post


All Replies
Super User
Posts: 6,938

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
Contributor
Posts: 30

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

 

Contributor
Posts: 30

Re: variable counting

Kurt,

How do you give the output a label. Currently it comes back as _TEMG001. Thanks.
Solution
a week ago
Occasional Contributor
Posts: 19

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;

Occasional Contributor
Posts: 19

Re: variable counting

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

Super User
Posts: 6,938

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
Occasional Contributor
Posts: 19

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;
Learner
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.

Need further help from the community? Please ask a new question.

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