BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
capam
Pyrite | Level 9

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

1 ACCEPTED SOLUTION

Accepted Solutions
PBsas
Obsidian | Level 7

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

8 REPLIES 8
Kurt_Bremser
Super User
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
Pyrite | Level 9

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
Pyrite | Level 9
Kurt,

How do you give the output a label. Currently it comes back as _TEMG001. Thanks.
PBsas
Obsidian | Level 7

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

PBsas
Obsidian | Level 7

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

Kurt_Bremser
Super User

@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
Obsidian | Level 7
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
Calcite | Level 5

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;

sas-innovate-2024.png

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.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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