Dear Tom, exactly I have 2 input data set: 1. the main data set with variables and observations data have; infile datalines dsd; input a b c d; datalines; xx, 5, 100, 3 xy, 5, 2, 3 xz, 5, 3, 3 xy, 5, 2, 3 run; 2. the summary data set obtained running a proc content and extending it with some external data: e.g. the maximum expected value and the minimum expected value data content; infile datalines dsd; input variable min max; datalines; a, -, - b, -50, 50 c, -50, 50 d, -50, 50 run; basically I wanna enahcne the summary as if follows: data content_res; infile datalines dsd; input variable min max out_up out_down; datalines; a, -, -, -, - b, -50, 50, 0, 0 c, -50, 50, 1, 0 d, -50, 50, 0, 0 run; The table I have is much bigger, this is just an example to better explain my problem. Thanks a lot for the suggestion but before to move to an evoluted you I would like step by step to fix the bus in mine and then further improve it: here the current version of my code: proc printto log="W:\03-OUT_outputfolder\SAS_LOG.txt" new; run; options mlogic SYMBOLGEN MPRINT; /* check 02: macro count outliers */ %macro macro_outliers (var=); proc sql; create table upper_bound as select Variable_max from DC.input_analysis_res where variable_name="&VAR" ;quit; proc sql; create table lower_bound as select Variable_min from DC.input_analysis_res where variable_name="&VAR" ;quit; proc sql; create table base as select distinct '&VAR' from in.test ;quit; proc sql; create table base as select a.*, b.*, c.* from base a, upper_bound b, lower_bound c ;quit; %if vartype='N' /* vartype(table,1)='N' */ %then %do; proc sql; create table num_ourliers as select (&VAR>Variable_max) as n_over, (&VAR<Variable_min) as n_under from base ;quit; proc sql; create table num_ourliers as select sum(n_over) as num_over, sum(n_under) as num_under from num_ourliers ;quit; proc sql; update DC.input_analysis_res set outliers_up = (select num_over FROM num_ourliers) where variable_name="&VAR" ;quit; proc sql; update DC.input_analysis_res set outliers_down = (select num_under FROM num_ourliers) where variable_name="&VAR" ;quit; %end; %else %do; %end; %mend macro_outliers; /* call macros */ data _null_; set DC.input_analysis_res; /* check 01: unexpected values if Variable_value_list not in ("") then call execute('%In_List (var='||strip(variable_name)||',vals='||strip(variable_value_list)||');');*/ /* check 02: count outliers */ if (Variable_min ne 0 and Variable_min ne 0) then call execute('%macro_outliers (var='||strip(variable_name)||');'); /* check 03: number of missing and default values attached a screen shot of the results I get, I marked in red the wrong one: since the maximum is 20.607 and the variable_max=10.000 I should get in the field outliers_up an integer equal or bigger than 1. call execute ('%macro_missing (var='||strip(variable_name)||',def_val='||strip(Default_value)||');');*/ run; proc printto; run;
... View more
