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;
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.