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
proc sql;
create table fault_count as
select fault_code, count(*) as fault_count
from all_units_results
group by fault_code;
quit;
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.
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
proc sql;
create table fault_count as
select fault_code, count(*) as fault_count
from all_units_results
group by fault_code;
quit;
Please change the soultion to the Kurt's post. He was the first to solve your question.
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;
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;
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.