Hi, I need help with a macro that was created by MikeZdeb for me. The macro currently counts the number of missing records, records with the value N/A and "non-missing records" as well as providing the total. Below is the code provided by MikeZdeb (you can see this post for more info https://communities.sas.com/thread/36410?start=15&tstart=0) First of all, I would be really greatful if someone could put comments in the code in order for me to properly understand what it is doing. I would also like the macro output to have one more column wich would indicate the number of duplicates records. I currently use the following sql commands to count the duplicates records and I don't think it is the most efficient way to do it.: proc sql; create table work.test as SELECT name, COUNT(name) AS NumOfccurrences FROM sasuser.blue_car_table GROUP BY name HAVING ( COUNT(name) > 1 ) ;quit; proc sql; select count(*) from work.test ;quit; I would like to know the proper code to counts the number of duplicates records and how to add it in the existing MikeZdeb's macro (as a new column). Thank you for your help and time. data table1; input name $ age @@ ; cards; John 45 N/A 30 . 15 Carl 25 ; data table2; input color $ height; cards; Blue 110 N/A 120 . 100 Red . ; proc format; value nn low-high='1' other='3'; value $c 'N/A'='2' ' '='3' other='1'; run; %macro tables(stuff); proc datasets lib=work nolist; delete tables; quit; %let j=1; %do %while(%scan(&stuff,&j,/) ne); %let dset=%scan(&stuff,&j,/); ods output onewayfreqs=temp (keep=table f_: freq:); proc freq data=&dset; tables _all_ / missing; format _numeric_ nn. _character_ $c.; run; ods output close; data temp (keep=table name not_: missing total); length table name $32; do until(last.table); set temp; by table notsorted; array t(*) f_:; select(t(_n_)); when('1') not_missing = frequency; when('2') not_applicable = frequency; when('3') missing = frequency; end; end; total = sum(of not_:, missing); name = scan(table,2); table = "&dset"; run; proc append base=tables data=temp; run; %let j=%eval(&j+1); %end; proc datasets lib=work nolist; delete temp; quit; %mend; ods listing close; %tables(table1/table2/sashelp.class/sashelp.heart); ods listing; partial output of data set TABLES ... not_ table name missing not_applicable missing total table1 name 2 1 1 4 table1 age 4 . . 4 z.table2 color 1 . . 1 z.table2 height 1 . . 1 sashelp.class Name 19 . . 19 sashelp.class Sex 19 . . 19 sashelp.class Age 19 . . 19 sashelp.class Height 19 . . 19 sashelp.class Weight 19 . . 19 sashelp.heart Status 5209 . . 5209 sashelp.heart DeathCause 1991 . 3218 5209 sashelp.heart AgeCHDdiag 1449 . 3760 5209
... View more
