Macro to count the number of records

[nicnad]

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!

[Amir]

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.

[nicnad]

Yes only the column names and table name will change.

Also the values in my column are all alphanumerical.

Thank you for your help.

[Amir]

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

N/A

123

12C

N/A

;

%countm(text,test);

Regards,

Amir.

[Reeza]

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

[nicnad]

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!

[Ksharp]

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

[nicnad]

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.

[Linlin]

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

[nicnad]

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: 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.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;

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
     

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        

[Linlin]

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

[nicnad]

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        

[Linlin]

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

[Dorota_Jarosz]

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;

[Ksharp]

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