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);
... View more