topic Re: variable counting in SAS Programming

variable counting

capam — Fri, 11 Aug 2017 18:19:04 GMT

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

Re: variable counting

Kurt_Bremser — Fri, 11 Aug 2017 18:29:26 GMT

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.

Re: variable counting

PBsas — Fri, 11 Aug 2017 18:37:11 GMT

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;

Re: variable counting

capam — Fri, 11 Aug 2017 18:40:15 GMT

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

Re: variable counting

capam — Fri, 11 Aug 2017 18:45:41 GMT

Kurt,

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

Re: variable counting

PBsas — Fri, 11 Aug 2017 18:47:53 GMT

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

Re: variable counting

PBsas — Fri, 11 Aug 2017 18:55:42 GMT

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

Re: variable counting

viditmalhotra89 — Sat, 12 Aug 2017 06:17:36 GMT

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;

Re: variable counting

Kurt_Bremser — Mon, 14 Aug 2017 06:38:10 GMT

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