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