Dear RW,   sorry but I am getting confuse, let me do a short recap: 1. DC.Input_analysis_res is created as a result of a proc contents (in this case of the file in.test) proc contents data=in.test out=DC.input_analysis noprint ;run; 2. some variables are dropped and other renamed: proc sql; create table DC.input_analysis as select (cats(trim(t.LIBNAME),trim(t.MEMNAME),trim(t.NAME))) as unique_ID_data_checks ,t.LIBNAME as library_name ,t.MEMNAME as data_set_name ,t.NAME as variable_name ,t.FORMAT ,t.LENGTH ,t.NOBS as observation_number ,t.MODATE as Last_Modified_Date from DC.input_analysis t ;run; 3. the following vatiables are added (e.g Variable_value_list) using an external data source: proc sql; create table DC.input_analysis as select t.* ,t1.Variable_Name_Original ,t1.Variable_Name_English ,t1.Variable_description /* variable description */ ,t1.Variable_value_list /* possible values (not comma separated!!!!!!) */ ,t1.Default_value ,t1.Variable_category /* Classification: date, amount, ID, key */ ,t1.Variable_min /* lower bound */ ,t1.Variable_max /* upper bound */ from DC.input_analysis t left outer join In.Variables_information t1 on (t.variable_name=t1.Variable_Name_Original or t.variable_name=t1.Variable_Name_English) ;run; 4. run the proc means and get already for the numeric variable in test the following information: proc means data=in.test STACKODS n nmiss range min max; var _numeric_; ods output summary=DC.stacked ;run; 5.Create the table DC.input_analysis_res where: the data from input analysis are taken and enhanced with the ones from DC.stacked and additionally 3 new variable are created (in bold): proc sql; create table DC.input_analysis_res as select t1.*, t2.*, 0 as outliers_up, 0 as outliers_down, " " as Unexpected_values from DC.input_analysis t1 left outer join DC.stacked t2 on t1.variable_name=t2.Variable ;quit; Till the point 5 everything is running smoothly. Now I should perform some checks that the command STACKODS was not able to do. For instance: find the value for each observation in DC.input_analysis_res, on the basis of Variable_value_list I would like to fill the variable Unexpected_values. How?Taking each observation of the variable variable_name in DC.input_analysis_res as variable in in.test and comparing the value of all observation for each variable with the value list given in DC.input_analysis_res (field Variable_value_list). In order to get what I want at the point 5, the macro you suggested seems to be the (almost) the perfect solution:   %macro In_list (var=,vals=); Options nomacrogen NoSymbolgen nomlogic nomprint nomfile; proc sql; insert into DC.input_analysis_res select distinct "Unexpected_values", variable_value_list in.test where &VAR. not in (&VALS.); quit; %mend In_List; data _null_; set DC.input_analysis_res; if Variable_value_list ne "" then call execute('%In_List (var='||strip(var)||',vals='||strip(variable_value_list)||');'); run;   Coming back to your question: in in.test there is no variable named Variable_value_list (this was the input and it should remanin unchanged, the value list is given for each variable by all the observations). in DC.input_analysis_res there is the variable Variable_value_list since it was taken as enahncement (left outer join) if the name would be worng, I would not get it in DC.input_analysis_res. Did we have the same understanding of the same Situation? Thanks a lot RW9.   ... View more

Dear RW9,   thanks for the indication, I now replaced all the "," used to separate the value in the value list with "_" and I do not get the error message anymore. 🙂 Anyway I am still struggling with a syntax error (attachment). Any idea? Thanks again. ... View more

Dear RW9,   it gets better and better and when it will be working I can also adapt the same macro for defferent purposes but still not get the data I want. I googled  for the error and I tryit to get more information from the debug (Options nomacrogen NoSymbolgen nomlogic nomprint nomfile.) but I do not get why the variable var is not initialized: ERROR: All positional parameters must precede keyword parameters. Attached the log. ... View more

Dear RW9,   the structure with the macrois at list for me better organized and I prefer it but still, I do not get what I want. 😞 Based on my understanding: 1. you check for each row of the table DC.input_analysis_res if the variable Variable_value_list is not empty, in case not you go to the next step; 2. using call execute you call the specified macro, defining the required inputs:       2a. var='||strip(var)||;       2b. vals=',strip(variable_value_list); 3. the macro recursively for each row, select the variable (colum) in the table test (e.g. Nationality) extracts all the values assumed (e.g. Jpanese, French...)  that are not  &vals; 4. the values selected in 3 are stored as variable "Unexpected_values" in DC.input_analysis_res.   Did I understand correctly? Here the part that I am using for the debug and attached the log.   %macro In_list (var=,vals=); proc sql; insert into DC.input_analysis_res select distinct "Unexpected_values", variable_value_list in.test where &VAR. not in (&VALS.); quit; %mend In_List; data _null_; set DC.input_analysis_res; if Variable_value_list ne "" then call execute('%In_List (var='||strip(var)||',vals=',strip(variable_value_list),');'); run; /* debug: proc sql; create table aaa as select &var., count(*) as n from in.test where &var. not in (&vals.) group by &var.; run; proc sql; create table aaa as select segment, count(*) as n from in.test where segment not in (&vals.) group by segment; run; */ ... View more

cool Kurt! Thanks a lot, i will then start playing with it. ... View more

log screenshot ... View more

Dear RW9,   I am trying to use your code but I am still doing something wron: here the the current version of my code:   data _null_; set DC.input_analysis_res; if Variable_value_list ne "" then do; call execute('proc sql; insert into DC.input_analysis_res select distinct "Unexpected_values", variable_value_list in.test where _all_ not in ("'||strip(tranwrd(variable_value_list,",",'","'))||'"); quit;'); end; /*variable_min ne "" then do; call execute('proc sql; insert into outliers_down select distinct "Not in range",PAYMENT_DATE from HAVE where PAYMENT_DATE not between input("'||strip(VARIABLE_MIN)||'",ddmmyy10.) and input("'||strip(VARIABLE_MAX)||'",ddmmyy10.); quit;'); end;*/ run;   Attached again the input file and the error message I got. I do not get the role of the variable you described "category". Since I should do the same check for all the the variables I called it _ALL_.  Further help on it is welcome. ... View more

thanks a lot for the suggestion... I will check it. ... View more

The question was related to the potentiality of SAS: how Do You Flowchart a SAS Program? The scope was to know if SAS can do it or not. So you don't plan/document your code before you start working on? That’s a too general question. It depends on the situation (e.g. complexity of the code, purpose of the analysis) anyway you can post it! I actually do not write a flochart each time that I am using a program for some analysis (r, SAS, Phyton or whatever)   ... View more

thanks Jan, I was not aware of it. ... View more

HI RW9, attached you find: - DC.input_analysis: input data set (data from the proc contents enhanced) - test: data set to be tested - expected results: in green the data that I already get running the proc means below:   proc means data=test STACKODS n nmiss range min max; var _numeric_; ods output summary=stacked ;run;   In yellow the data I would like to get in an easy manner. My idea was: take individually each variable of the data set 'DC.input_analysis' (Macro + loop) and store the result obtained in 'DC.input_analysis'.   Thanks for your support, ... View more