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!
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.
Yes only the column names and table name will change.
Also the values in my column are all alphanumerical.
Thank you for your help.
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.
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;
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 :
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!
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
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.
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
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
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
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
I use PC sas 9.3. what version of sas do you use?
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;
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
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Select SAS Training centers are offering in-person courses. View upcoming courses for: