topic Re: Macro to count the number of records in SAS Programming

Macro to count the number of records

nicnad — Thu, 19 Jul 2012 16:26:21 GMT

Hi,

I am a beginner with SAS. I want to write a maco that would count the records from multiple tables based on preset conditions. Here is what I have written so far, but it is not working. I would also want to add the posibility count different coulmn for different table in the macro, but I would still want to have the same conditions and I would want it to be displayed in the same table. Can you please help me write this macro?

options nodate pageno=1 linesize=80 pagesize=60;

%macro countm(col);

count(&col) "Non Null",

count(&col) where &col is null "Null",

count(&col) where &col='N/A' "N/A/"

%mend;

proc sql;

title 'Counts for Each Type of Missing Response';

select count(*) "Total # of Rows",

%countm(value1)

from test1;

Thank you for your help!

Re: Macro to count the number of records

Amir — Thu, 19 Jul 2012 16:45:58 GMT

Hi,

I'm no expert, but I don't think proc sql would allow two where statements in a single select, which is what appears in the macro function countm.

It might be worth providing some sample input data and required output data to better explain what you need.

Am I right in understanding that just the table name & column names will change and everything else will remain the same?

Regards,

Amir.

Message was edited by: Amir Malik - minor correction.

Re: Macro to count the number of records

nicnad — Thu, 19 Jul 2012 17:42:49 GMT

Yes only the column names and table name will change.

Also the values in my column are all alphanumerical.

Thank you for your help.

Re: Macro to count the number of records

Amir — Thu, 19 Jul 2012 17:51:47 GMT

Hi,

Based on the original code, I came up with the following, but the "is null" test needs fixing, but I have to go now because the building I'm in is about to shut! Hope it helps.

options nodate pageno=1 linesize=80 pagesize=60;

title 'Counts for Each Type of Missing Response';

%macro countm(col,tname);

proc sql;

select count(*) "Total # of Rows"

from &tname

;

select count(&col) "Non Null"

from &tname

;

select count(&col) "Null"

from &tname

where &col is null

;

select count(&col) "N/A"

from &tname

where &col='N/A'

;

quit;

%mend countm;

data test;

input text $char3.;

datalines;

AAA

BBB

N/A

123

12C

N/A

;

%countm(text,test);

Regards,

Amir.

Re: Macro to count the number of records

Reeza — Thu, 19 Jul 2012 19:04:56 GMT

See Example 2 in this paper.

You can add your N/A in to the format and see what that does.

proc format;

value $ missfmt

' '="Missing"

'N/A' = 'Not Applicable'

other="Not Missing"

;

run;

http://www2.sas.com/proceedings/sugi30/001-30.pdf

Re: Macro to count the number of records

nicnad — Thu, 19 Jul 2012 19:57:06 GMT

Thank you very much for the reply.

We are getting real closer to what I am looking to do.

I am a real beginner in SAS so my following requirements are probably easy to do :

I do not want to have the Percent Cumulative, Frequency Cumulative,Percent ; Is there a way to hide those information?
Instead of having separate tables for each variable, is there a way to put all the result in a same table?
Is there a way to do this for multiple tables and have the result in a single table?

Basicly what I would like to have would look like this :

Lets say I have those 2 tables :

Table 1
Name	Age
John	45
N/A	30
	15
Carl	25

Table 1
Color	Height
Blue	110
N/A	120
	100
Red

I would like to get this result :

	Table 1		Table 2
	Name	Age	Color	Height
Missing	1	0	1	1
Not Applicable	1	0	1	0
Not Missing	2	4	2	3
Total # Records	4		4

Can someone please help me write a SAS procedure or macro that would print an output that would look like this?

Thank you for your help and time!

Re: Macro to count the number of records

Ksharp — Fri, 20 Jul 2012 05:49:16 GMT

Assuming all your tables are under WORK library.

data table1;
input Name $     Age ;
cards;
John     45
N/A     30
.     15
Carl     25
;
run;
data table2;
input Color $     Height ;
cards;
Blue     110
N/A     120
.     100
Red     .
;
run;
data _null_;
 set sashelp.vcolumn(keep=name memname libname where=(libname='WORK')) end=last;
 if _n_ eq 1 then  call execute("proc sql;create table temp as") ;
 call execute("select '"||strip(memname)||"' as table,'"||strip(name)||"' as "||strip(name)||",nmiss("||strip(name)||") as missing,sum(case when cats("||strip(name)||")='N/A' then 1 else 0 end) as Not_Applicable,sum(case when cat("||strip(name)||") not in ('N/A' ' ' '.') then 1 else 0 end) as Not_Missing from "||strip(memname) );
 if  not last then call execute("union all");
 if last then call execute(";quit;");
run;

proc transpose data=temp out=want;
id table name;
var Missing Not_Applicable Not_Missing;
run;

Ksharp

Re: Macro to count the number of records

nicnad — Fri, 20 Jul 2012 15:45:12 GMT

Thank you very much ksharp for the reply.

I get the following error and the table Want is empty :

Could you please help me solve this? Also, how do I select specific table that are not in the libname "WORK".

E.G.

sasuser.table1

sasuser.table2

NOTE: Some of your options or statements may not be supported with the Activex or Java series of devices. Graph defaults for these
      drivers may be different from other SAS/GRAPH device drivers. For further information, please contact Technical Support.
9          OPTIONS DEV=ACTIVEX;
10         FILENAME EGHTML TEMP;
NOTE: Writing HTML(EGHTML) Body file: EGHTML
11         ODS HTML(ID=EGHTML) FILE=EGHTML ENCODING='utf-8' STYLE=EGDefault
11       ! STYLESHEET=(URL="file:///C:/Program%20Files/SAS/Shared%20Files/BIClientStyles/EGDefault.css")
11       ! ATTRIBUTES=("CODEBASE"="http://www2.sas.com/codebase/graph/v91/sasgraph.exe") NOGTITLE NOGFOOTNOTE GPATH=&sasworklocation
11       ! ;
12
13         %gaccessible;
14         data table1;
15         input Name $     Age ;
16         cards;

NOTE: The data set WORK.TABLE1 has 4 observations and 2 variables.
NOTE: DATA statement used (Total process time):
      real time           0.01 seconds
      cpu time            0.00 seconds

21         ;
22         run;
23         data table2;
24         input Color $     Height ;
25         cards;

NOTE: The data set WORK.TABLE2 has 4 observations and 2 variables.
30         ;
NOTE: DATA statement used (Total process time):
      real time           0.01 seconds
      cpu time            0.00 seconds

31         run;
32         data _null_;
33          set sashelp.vcolumn(keep=name memname libname where=(libname='WORK')) end=last;
34          if _n_ eq 1 then call execute("proc sql;create table temp as") ;
35          call execute("select '"||strip(memname)||"' as table,'"||strip(name)||"' as "||strip(name)||",nmiss("||strip(name)||")
35       ! as missing,sum(case when cats("||strip(name)||")='N/A' then 1 else 0 end) as Not_Applicable,sum(case when
35       ! cat("||strip(name)||") not in ('N/A' ' ' '.') then 1 else 0 end) as Not_Missing from "||strip(memname) );
36          if not last then call execute("union all");
37          if last then call execute(";quit;");
38         run;

NOTE: DATA statement used (Total process time):
      real time           7:54.09
      cpu time            26.02 seconds

NOTE: There were 4 observations read from the data set SASHELP.VCOLUMN.
2                                                          The SAS System                                11:14 Friday, July 20, 2012

WHERE libname='WORK';

NOTE: CALL EXECUTE generated line.
1         + proc sql;
1         +          create table temp as
2         + select 'TABLE1' as table,'Name' as Name,nmiss(Name) as missing,sum(case when cats(Name)='N/A' then 1 else 0 end) as
Not_Applicable,sum(case when cat(Name) not in ('N/A' ' ' '.') then 1 else 0 end) as Not_Missing from TABLE1
3         + union all
4         + select 'TABLE1' as table,'Age' as Age,nmiss(Age) as missing,sum(case when cats(Age)='N/A' then 1 else 0 end) as
Not_Applicable,sum(case when cat(Age) not in ('N/A' ' ' '.') then 1 else 0 end) as Not_Missing from TABLE1
5         + union all
6         + select 'TABLE2' as table,'Color' as Color,nmiss(Color) as missing,sum(case when cats(Color)='N/A' then 1 else 0 end) as
Not_Applicable,sum(case when cat(Color) not in ('N/A' ' ' '.') then 1 else 0 end) as Not_Missing from TABLE2
7         + union all
8         + select 'TABLE2' as table,'Height' as Height,nmiss(Height) as missing,sum(case when cats(Height)='N/A' then 1 else 0
end) as Not_Applicable,sum(case when cat(Height) not in ('N/A' ' ' '.') then 1 else 0 end) as Not_Missing from TABLE2
9         + ;
9         + quit;
NOTE: Table WORK.TEMP created, with 4 rows and 5 columns.

NOTE: PROCEDURE SQL used (Total process time):
      real time           0.40 seconds
      cpu time            0.00 seconds

39
40         proc transpose data=temp out=want;
41         id table name;
                    _____
                    22 200
ERROR 22-322: Expecting ;.
ERROR 200-322: The symbol is not recognized and will be ignored.
42         var Missing Not_Applicable Not_Missing;
43         run;

NOTE: The SAS System stopped processing this step because of errors.
WARNING: The data set WORK.WANT may be incomplete. When this step was stopped there were 0 observations and 0 variables.
NOTE: PROCEDURE TRANSPOSE used (Total process time):
      real time           0.01 seconds
      cpu time            0.00 seconds

44
45
46         %LET _CLIENTTASKLABEL=;
47         %LET _EGTASKLABEL=;
48         %LET _CLIENTPROJECTNAME=;
49         %LET _SASPROGRAMFILE=;
50
51         ;*';*";*/;quit;run;
52         ODS _ALL_ CLOSE;
53
54
55         QUIT; RUN;
56

Thank you very much for your help and time.

Re: Macro to count the number of records

Linlin — Fri, 20 Jul 2012 16:30:32 GMT

Hi,

replacing the RED parts with your real info. and using capital letters where I used.

/*data table1;
input name $     Age ;
cards;
John     45
N/A     30
.     15
Carl     25
;
run;
data table2;
input Color $     Height ;
cards;
Blue     110
N/A     120
.     100
Red     .
;
run; */

%let firstvar=name;/* the macro variable should be the first variable in your first dataset,in Ksharp's example,firstvar=name */
data _null_;
set sashelp.vcolumn(keep=name memname libname where=(libname='SASUSER' and memname in ('TABLE1','TABLE2'))) end=last;
if _n_ eq 1 then call execute("proc sql;create table temp as") ;
call execute("select '"||strip(memname)||"' as table,'"||strip(name)||"' as "||strip(name)||",nmiss("||strip(name)||") as missing,sum(case when cats("||strip(name)||")='N/A' then 1 else 0 end) as Not_Applicable,sum(case when cat("||strip(name)||") not in ('N/A' ' ' '.') then 1 else 0 end) as Not_Missing from sasuser."||strip(memname) );
if not last then call execute("union all");
if last then call execute(";quit;");
run;

proc transpose data=temp out=want;
id table &firstvar;
var Missing Not_Applicable Not_Missing;
run;
proc print data=want;run;

Linlin

Re: Macro to count the number of records

nicnad — Fri, 20 Jul 2012 18:13:26 GMT

Thank you Linlin for the reply.

I still get an error when I try to run the code. The two tables are named table1 and table2 and they are in WORK library.

Can you please help me fix the issue?

Also, as a nice to have and only if it is easy to do, is there a way for sas to read the table first variable and determine by itself what is the name of the first variable instead of having to write name?

I am a beginner with SAS but if I interpret the code right, should'nt the name in this line be replaced by &firstvar?

call execute("select '"||strip(memname)||"' as table,'"||strip(name)||"' as "||strip(name)||",nmiss("||strip(name)||") as missing,sum(case when cats("||strip(name)||")='N/A' then 1 else 0 end) as Not_Applicable,sum(case when cat("||strip(name)||") not in ('N/A' ' ' '.') then 1 else 0 end) as Not_Missing from sasuser."||strip

Finally, my real tables have 2 million + rows, I see that this code takes a while to load only for 4 rows tables. Is there an easy way to speed up the code?

Again, thank you all for your time and help. I am learning a lot. It is really appreciated.

Here is the error code :

NOTE: The data set WORK.TABLE1 has 4 observations and 2 variables.
NOTE: DATA statement used (Total process time):
      real time           0.00 seconds
      cpu time            0.01 seconds

21         ;
22         run;
23         data table2;
24         input Color $     Height ;
25         cards;

NOTE: The data set WORK.TABLE2 has 4 observations and 2 variables.
30         ;
NOTE: DATA statement used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds

31         run;
32
33
34
35         %let firstvar=name;/* the macro variable should be the first variable in your first dataset,in Ksharp's
35       ! example,firstvar=name */
36         data _null_;
37         set sashelp.vcolumn(keep=name memname libname where=(libname='WORK' and memname in ('TABLE1','TABLE2'))) end=last;
38         if _n_ eq 1 then call execute("proc sql;create table temp as") ;
39         call execute("select '"||strip(memname)||"' as table,'"||strip(name)||"' as "||strip(name)||",nmiss("||strip(name)||") as
39       ! missing,sum(case when cats("||strip(name)||")='N/A' then 1 else 0 end) as Not_Applicable,sum(case when
39       ! cat("||strip(name)||") not in ('N/A' ' ' '.') then 1 else 0 end) as Not_Missing from work."||strip(memname) );
40         if not last then call execute("union all");
41         if last then call execute(";quit;");
42         run;

2 The SAS System 13:43 Friday, July 20, 2012

NOTE: DATA statement used (Total process time):
      real time           8:52.94
      cpu time            23.37 seconds

NOTE: There were 4 observations read from the data set SASHELP.VCOLUMN.
      WHERE (libname='WORK') and memname in ('TABLE1', 'TABLE2');

NOTE: CALL EXECUTE generated line.
1         + proc sql;
1         +          create table temp as
2         + select 'TABLE1' as table,'name' as name,nmiss(name) as missing,sum(case when cats(name)='N/A' then 1 else 0 end) as
Not_Applicable,sum(case when cat(name) not in ('N/A' ' ' '.') then 1 else 0 end) as Not_Missing from work.TABLE1
3         + union all
4         + select 'TABLE1' as table,'Age' as Age,nmiss(Age) as missing,sum(case when cats(Age)='N/A' then 1 else 0 end) as
Not_Applicable,sum(case when cat(Age) not in ('N/A' ' ' '.') then 1 else 0 end) as Not_Missing from work.TABLE1
5         + union all
6         + select 'TABLE2' as table,'Color' as Color,nmiss(Color) as missing,sum(case when cats(Color)='N/A' then 1 else 0 end) as
Not_Applicable,sum(case when cat(Color) not in ('N/A' ' ' '.') then 1 else 0 end) as Not_Missing from work.TABLE2
7         + union all
8         + select 'TABLE2' as table,'Height' as Height,nmiss(Height) as missing,sum(case when cats(Height)='N/A' then 1 else 0
end) as Not_Applicable,sum(case when cat(Height) not in ('N/A' ' ' '.') then 1 else 0 end) as Not_Missing from work.TABLE2
9         + ;
9         + quit;
NOTE: Table WORK.TEMP created, with 4 rows and 5 columns.

NOTE: PROCEDURE SQL used (Total process time):
      real time           0.06 seconds
      cpu time            0.01 seconds

43
44         proc transpose data=temp out=want;
45         id table &firstvar;
                             _
                             200
NOTE: Line generated by the macro variable "FIRSTVAR".
45          name
            ____
            22
ERROR 200-322: The symbol is not recognized and will be ignored.
ERROR 22-322: Expecting ;.
46         var Missing Not_Applicable Not_Missing;
47         run;

48 proc print data=want;run;

NOTE: No variables in data set WORK.WANT.
NOTE: PROCEDURE PRINT used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds
3                                                          The SAS System                                13:43 Friday, July 20, 2012

49
50
51
52
53
54         %LET _CLIENTTASKLABEL=;
55         %LET _EGTASKLABEL=;
56         %LET _CLIENTPROJECTNAME=;
57         %LET _SASPROGRAMFILE=;
58
59         ;*';*";*/;quit;run;
60         ODS _ALL_ CLOSE;
61
62
63         QUIT; RUN;
64

Re: Macro to count the number of records

Linlin — Fri, 20 Jul 2012 19:38:33 GMT

Yes. it is very slow. The code below works for me:

data table1;
input name $     Age ;
cards;
John     45
N/A     30
.     15
Carl     25
;
run;
data table2;
input Color $     Height ;
cards;
Blue     110
N/A     120
.     100
Red     .
;
run;

%let firstvar=name;/* the macro variable should be the first variable in your first dataset,in Ksharp's example,firstvar=name */
data _null_;
set sashelp.vcolumn(keep=name memname libname where=(libname='WORK' and memname in ('TABLE1','TABLE2'))) end=last;
if _n_ eq 1 then call execute("proc sql;create table temp as") ;
call execute("select '"||strip(memname)||"' as table,'"||strip(name)||"' as "||strip(name)||",nmiss("||strip(name)||") as missing,sum(case when cats("||strip(name)||")='N/A' then 1 else 0 end) as Not_Applicable,sum(case when cat("||strip(name)||") not in ('N/A' ' ' '.') then 1 else 0 end) as Not_Missing from work."||strip(memname) );
if not last then call execute("union all");
if last then call execute(";quit;");
run;

proc transpose data=temp out=want;
id table &firstvar;
var Missing Not_Applicable Not_Missing;
run;
proc print data=want;run;
Obs _NAME_ TABLE1name TABLE1Age TABLE2Color TABLE2Height

          1     missing                1            0             1               1
          2     Not_Applicable         1            0             1               0
          3     Not_Missing            2            4             2               3

/* log file */

NOTE: CALL EXECUTE generated line.
1   + proc sql;
1   +          create table temp as
2   + select 'TABLE1' as table,'name' as name,nmiss(name) as missing,sum(case when
cats(name)='N/A' then 1 else 0 end) as Not_Applicable,sum(case when cat(name) not in ('N/A' ' '
'.') then 1 else 0 end) as Not_Missing from work.TABLE1
3   + union all
4   + select 'TABLE1' as table,'Age' as Age,nmiss(Age) as missing,sum(case when cats(Age)='N/A'
then 1 else 0 end) as Not_Applicable,sum(case when cat(Age) not in ('N/A' ' ' '.') then 1 else 0
end) as Not_Missing from work.TABLE1
5   + union all
6   + select 'TABLE2' as table,'Color' as Color,nmiss(Color) as missing,sum(case when
cats(Color)='N/A' then 1 else 0 end) as Not_Applicable,sum(case when cat(Color) not in ('N/A' ' '
'.') then 1 else 0 end) as Not_Missing from work.TABLE2
7   + union all
8   + select 'TABLE2' as table,'Height' as Height,nmiss(Height) as missing,sum(case when
cats(Height)='N/A' then 1 else 0 end) as Not_Applicable,sum(case when cat(Height) not in ('N/A' '
' '.') then 1 else 0 end) as Not_Missing from work.TABLE2
9   + ;
NOTE: Table WORK.TEMP created, with 4 rows and 5 columns.

9   + quit;
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.03 seconds
      cpu time            0.01 seconds

903
904 proc transpose data=temp out=want;
905 id table &firstvar;
906 var Missing Not_Applicable Not_Missing;
907 run;

NOTE: There were 4 observations read from the data set WORK.TEMP.
NOTE: The data set WORK.WANT has 3 observations and 5 variables.
NOTE: PROCEDURE TRANSPOSE used (Total process time):
real time 0.03 seconds
cpu time 0.01 seconds

908 proc print data=want;run;

NOTE: There were 3 observations read from the data set WORK.WANT.
NOTE: PROCEDURE PRINT used (Total process time):
real time 0.02 seconds
cpu time 0.00 seconds

Linlin

Re: Macro to count the number of records

nicnad — Fri, 20 Jul 2012 20:22:12 GMT

For some reason I still get an error.

The temp table is successfully created, but it is the proc transpose that cause an error.

Can you please help me find what is causing the error with the proc transpose?

Here is the code I run :

data table1;
input name $     Age ;
cards;
John     45
N/A     30
.     15
Carl     25
;
run;
data table2;
input Color $     Height ;
cards;
Blue     110
N/A     120
.     100
Red     .
;
run;

%let firstvar=name;/* the macro variable should be the first variable in your first dataset,in Ksharp's example,firstvar=name */
data _null_;
set sashelp.vcolumn(keep=name memname libname where=(libname='WORK' and memname in ('TABLE1','TABLE2'))) end=last;
if _n_ eq 1 then call execute("proc sql;create table temp as") ;
call execute("select '"||strip(memname)||"' as table,'"||strip(name)||"' as "||strip(name)||",nmiss("||strip(name)||") as missing,sum(case when cats("||strip(name)||")='N/A' then 1 else 0 end) as Not_Applicable,sum(case when cat("||strip(name)||") not in ('N/A' ' ' '.') then 1 else 0 end) as Not_Missing from work."||strip(memname) );
if not last then call execute("union all");
if last then call execute(";quit;");
run;

proc transpose data=temp out=want;
id table &firstvar;
var Missing Not_Applicable Not_Missing;
run;
proc print data=want;run;

and here is the error that I am getting :

1          ;*';*";*/;quit;run;
2          OPTIONS PAGENO=MIN;
3          %LET _CLIENTTASKLABEL=%NRBQUOTE(Code);
4          %LET _EGTASKLABEL=%NRBQUOTE(Code);
5          %LET _CLIENTPROJECTNAME=%NRBQUOTE();
6          %LET _SASPROGRAMFILE=;
7
8          ODS _ALL_ CLOSE;
NOTE: Some of your options or statements may not be supported with the Activex or Java series of devices. Graph defaults for these
      drivers may be different from other SAS/GRAPH device drivers. For further information, please contact Technical Support.
9          OPTIONS DEV=ACTIVEX;
10         FILENAME EGHTML TEMP;
NOTE: Writing HTML(EGHTML) Body file: EGHTML
11         ODS HTML(ID=EGHTML) FILE=EGHTML ENCODING='utf-8' STYLE=EGDefault
11       ! STYLESHEET=(URL="file:///C:/Program%20Files/SAS/Shared%20Files/BIClientStyles/EGDefault.css")
11       ! ATTRIBUTES=("CODEBASE"="http://www2.sas.com/codebase/graph/v91/sasgraph.exe") NOGTITLE NOGFOOTNOTE GPATH=&sasworklocation
11       ! ;
12
13         %gaccessible;
14         proc transpose data=temp out=want;
15         id table &firstvar;
                             _
                             200
NOTE: Line generated by the macro variable "FIRSTVAR".
15          name
            ____
            22
ERROR 200-322: The symbol is not recognized and will be ignored.
ERROR 22-322: Expecting ;.
16         var Missing Not_Applicable Not_Missing;
17         run;

NOTE: The SAS System stopped processing this step because of errors.
WARNING: The data set WORK.WANT may be incomplete. When this step was stopped there were 0 observations and 0 variables.
WARNING: Data set WORK.WANT was not replaced because this step was stopped.
NOTE: PROCEDURE TRANSPOSE used (Total process time):
      real time           0.21 seconds
      cpu time            0.00 seconds

18 proc print data=want;run;

NOTE: No variables in data set WORK.WANT.
NOTE: PROCEDURE PRINT used (Total process time):
      real time           0.01 seconds
      cpu time            0.00 seconds

19
20         %LET _CLIENTTASKLABEL=;
21         %LET _EGTASKLABEL=;
22         %LET _CLIENTPROJECTNAME=;
23         %LET _SASPROGRAMFILE=;
24
25         ;*';*";*/;quit;run;
26         ODS _ALL_ CLOSE;
2                                                          The SAS System                                15:55 Friday, July 20, 2012

27
28
29         QUIT; RUN;
30

Re: Macro to count the number of records

Linlin — Fri, 20 Jul 2012 22:12:09 GMT

I use PC sas 9.3. what version of sas do you use?

Re: Macro to count the number of records

Dorota_Jarosz — Sat, 21 Jul 2012 00:18:16 GMT

Nicnad,

It looks like you are using SAS Enterprise Guide (EG), which submits some initial statements before anything you actually manually submit.

I am not getting any error when submitting the above code. You may have some left overs from the previous runs. Can you start a new session and run this code in clean environment? If you could launch pure SAS session it would be even better.

Try the code below - I renamed few things to avoid conflicts in naming. It would be safer if you copy this code into Notepad first and then copy pure text into SAS EG Program window. When you copy from a web site some invisible characters may get copied and obstruct correct processing. This may be the reason you are getting "Expecting ;." error.

options nodate nocenter pageno=1 MPRINT MLOGIC;

data table1_;
input Name $ Age ;
cards;
John     45
N/A      30
.        15
Carl     25
;
run;
data table2_;
input Color $ Height ;
cards;
Blue     110
N/A      120
.        100
Red      .
;
run;

%let firstvar=name;
* the macro variable should be the first variable in your first dataset, in Ksharp's example firstvar=name ;

data _null_;
set sashelp.vcolumn(keep=name memname libname where=(libname='WORK' and memname in ('TABLE1_','TABLE2_'))) end=last;
if _n_ eq 1 then call execute("proc sql; create table temp1 as") ;
call execute(
"select '"||strip(memname)||"' as table,
        '"||strip(name)||"' as "||strip(name)||",
        nmiss("||strip(name)||") as Missing,
        sum(case when cats("||strip(name)||")='N/A' then 1 else 0 end) as Not_Applicable,
        sum(case when cat("||strip(name)||") not in ('N/A' ' ' '.') then 1 else 0 end) as Not_Missing from work."||strip(memname));
if not last then call execute("union all");
if last then call execute(";quit;");
run;

proc transpose data=temp1 out=want (rename=(_Name_=Num_Obs));
id table &firstvar;
var Missing Not_Applicable Not_Missing;
run;
proc print data=want;
title "Counts of missing, not applicable and nonmissing obs";
run;

Re: Macro to count the number of records

Ksharp — Mon, 23 Jul 2012 05:21:41 GMT

First of all , you can copy whatever dataset you want into WORK ,then run my code.

And I guess your SAS is 9.1 ,so you can't run id statement of transpose;

proc transpose data=temp out=want;

id table name;

If that is, TEMP dataset is good enough for you

Ksharp

Re: Macro to count the number of records

nicnad — Mon, 23 Jul 2012 20:27:29 GMT

Thank you for the reply.

I am using SAS 9.1 so this is why the transpose portion of the code was not working.

First of all, thank you very much for this code. Two questions :

I tried to run your code on 10 tables and I got the following error : "ERROR: Query is too complex to be processed. It references more than 16 tables". Is there a way to bypass this error? Would deleting the temp table when a new one is created solve this problem or am I not understanding the error code properly?
How do I add a column to the table that would give me the sum of each of the three already existing column ? (See example below)

table	Name	Missing	Not_Applicable	Not_Missing	Total
TABLE1_	Name	1	1	2	4
TABLE1_	Age	0	0	4	4
TABLE2_	Color	1	1	2	4
TABLE2_	Height	1	0	3	4

Thank you for your help and time.

Re: Macro to count the number of records

MikeZdeb — Mon, 23 Jul 2012 22:50:58 GMT

hi ... not sure why you get that message (Ksharp's code is pretty neat and normally very reliable)

here's an alternative that I think is a bit faster and generic enough that all you have to do is list the data sets with a '/' separator ...

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;

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

Re: Macro to count the number of records

Ksharp — Tue, 24 Jul 2012 03:27:09 GMT

Oh. I understand that ERROR message.

SQL's UNION clause only can support 250 select clause , so maybe you have too many tables and too many variables needed to process.

data table1;
input Name $     Age ;
cards;
John     45
N/A     30
.     15
Carl     25
;
run;
data table2;
input Color $     Height ;
cards;
Blue     110
N/A     120
.     100
Red     .
;
run;

options missing=' ';
data _null_;
 set sashelp.vcolumn(keep=name memname libname where=(libname='WORK')) end=last;
 if _n_ eq 1 then  call execute("proc sql;");
 call execute("create table temp"||strip(_n_)||" as select '"||strip(memname)||"' length=40 as table,'"||strip(name)||"' length=40 as Name,nmiss("||strip(name)||") as missing,sum(case when cats("||strip(name)||")='N/A' then 1 else 0 end) as Not_Applicable,sum(case when cat("||strip(name)||") not in ('N/A' ' ') then 1 else 0 end) as Not_Missing,count(*) as Total from "||strip(memname)||";" );
 if last then call execute(";quit;");
run;
data want;
 set temp:;
run;

Ksharp

Re: Macro to count the number of records

nicnad — Tue, 24 Jul 2012 14:43:52 GMT

Thank you both for your reply. Really appreciated.

@ Ksharp : Your code works great. Thank you for your help and time.

@ MikeZdeb : I don't get the same output as you. I would really like to get the same output as the one you provided in your post.

If we take my two example tables, here is the result I get :

Bitmap
The FREQ Procedure
name	Frequency	Percent	Cumulative	Cumulative
name	Frequency	Percent	Frequency	Percent
3	1	25.00	1	25.00
1	2	50.00	3	75.00
2	1	25.00	4	100.00
age	Frequency	Percent	Cumulative	Cumulative
age	Frequency	Percent	Frequency	Percent
1	4	100.00	4	100.00
The FREQ Procedure
color	Frequency	Percent	Cumulative	Cumulative
color	Frequency	Percent	Frequency	Percent
3	1	25.00	1	25.00
1	2	50.00	3	75.00
2	1	25.00	4	100.00
height	Frequency	Percent	Cumulative	Cumulative
height	Frequency	Percent	Frequency	Percent
3	1	25.00	1	25.00
1	3	75.00	4	100.00

I am running SAS Enterprise Guide 4.1 with SAS 9.1

Can you please help me get the same output as you?

Thank you for your help

Re: Macro to count the number of records

MikeZdeb — Tue, 24 Jul 2012 20:59:26 GMT

Hi ... did you check to see if there was also a data set named TABLES ...

proc print data=tables;

run;

when the macro finished?