topic Re: calculating sum and counts in SAS Programming

calculating sum and counts

Jagadeesh2907 — Sun, 28 Oct 2018 11:50:09 GMT

Hi , I am currently trying to calculate count of not null attributes and their sum and store it in another dataset. For example, If i have dataset work.students which has attributes ID, Name, age, fees etc. I need to calculate the count if ID, Name, age and fees where ID is not null and sum of fees where ID is not null (no groupings - just the total count and sum). My output needs to be like below:

attribute_name approach value

id count 100

name count 100

age count 100

fees count 100

fees sum 1,50,450

Currently I am using the below macros to achieve this. But is there a simpler way in sas to do this ?

%macro basetable(lib,dst,att);
proc sql;
select count(&att.) into :cnt
from &lib..&dstname.;
quit;

data work.base_dst_completeness;
table_name = "&dst." ;
approach = "count";
length attribute_name $50;
attribute_name = "&att.";
value = &cnt.;

run;
%mend basetable;

%basetable(work,student,id);

%macro calculate_value(part,att,varb,miss);
/* check condition if part is sum or count */

%if &part. = sum %then %do;
proc sql;
create table work.temp_dst_1 as
select "sum" as approach,
"&att." as attribute_name length=50,
&part.(&att.) as value
from work.students
where &varb. is &miss.;
quit;
%put "calculate sum";
%end;
%else %do;
proc sql;
create table work.temp_dst_1 as
select 'count' as approach,
"&att." as attribute_name length=50,
&part.(&att.) as value
from work.students
where &varb. is &miss.;
quit;
%put "calculate count ";
%end;

/* append the dataset with the base dataset */
proc append base = work.base_dst_completeness data=work.temp_dst_1;
run;

%mend calculate_value;

%calculate_value(count,name,id,not null);
%calculate_value(count,age,id,not null);
%calculate_value(count,fees,id,not null);
%calculate_value(sum,fees,fees,not null);

Re: calculating sum and counts

Jagadishkatam — Sun, 28 Oct 2018 12:41:13 GMT

proc sql;
create table  vars as select name from dictionary.columns where libname='WORK' and memname='STUDENTS';
select name into: vars1 separated by ' ' from vars;
quit;

data _null_;
set vars;
call execute('proc sql;create table '||name||' as select "'|| name||'" as name, count('||name||') as value, "count" as approach from work.students;quit;');
if lowcase(name)='fees' then call execute('proc sql;create table sum_'||name||' as select "'|| name||'" as name, sum('||name||') as value, "sum" as approach from work.students;quit;');
run;

data want;
set &vars1 sum:;
run;

Re: calculating sum and counts

PaigeMiller — Sun, 28 Oct 2018 15:01:05 GMT

Currently I am using the below macros to achieve this. But is there a simpler way in sas to do this ?

SAS has written code to do this, added many many many useful features, then debugged it, documented it, and said code has been proven effective in a bazillion, two hundred thousand and 48 real world applications.

PROC FREQ will do the counting for you.

PROC MEANS/PROC SUMMARY will compute means for you.

Don't write your own code to do this.

Re: calculating sum and counts

Jagadeesh2907 — Tue, 30 Oct 2018 10:55:08 GMT

Thank you PaigeMiller,

I tried Proc Means - Yes it help but only for numeric values and proc freq which provides a data level frequency than a attribute level count which is my need. That is why i wrote that macro. However, i felt my macro can be converted into a single one as i use first macro for creating a dataset to which i can append other datasets from second macro. Can you give me any tips ?

Re: calculating sum and counts

PaigeMiller — Tue, 30 Oct 2018 11:02:18 GMT

PROC FREQ will do what you want.

However, you have to show us a portion of your actual input data if you want a more detailed reply. As stated above, there is no need to write your own macro here. I consider it an extreme waste of time to write a macro to replicate the functionality of PROC MEANS and PROC FREQ.

Re: calculating sum and counts

Jagadeesh2907 — Thu, 15 Nov 2018 10:11:23 GMT

Hi PaigeMiller,

Thank you for your reply. Sorry for the late response as i was away. My requirement is pretty simple. If a data set has 5 attributes, i want the count of all 5 attributes (excluding the nulls for that attribute) and sum of only numeric attribute from that 5 attributes. If 2 out of 5 are numeric, i need both count and sum of the attributes.

Eg: If dataset has 5 attributes and 200 records,

attribute1 (Character column) - count = 200 (My Key column)

attribute 2 (character) - count = 180 (20 are null values)

attribute3 (numeric) - count = 200 sum = 4500 (no nulls and sum calculated)

etc.