Brilliant guys! I really appreciate our suggestions for improvement... hopefully by the end of the day I will come back to SAS and I will let you know. SH ... View more

Thanks a lot! I will try and let you know... ... View more

dear ballardw,   thanks a lot for your hint, I am trying to play with it. Still about the background: I run the proc contents on a table that I would like to analyze, I extendend the proc contents with some values (value lists, default values, number_of_missing, val_max, val_min, outliers_up, outliers_down values_not_in_v_list) taken from an external excel file. Some of these values in the extended proc contents are used merely as input to perform the checks (e.g. value list and default value or val_max and val_min) otherone should be calculated (e.g. outliers_up and outliers_down or values_not_in_v_list). basically I am executing 3 macro (currently only ones run perfectly thanks to rheinard) in order to get all the fields of the extended proc content filled (of course if meaninfull, if a variable has a value list, I will not calculate the outliers_up and viceversa). I will keep you posted. Thanks again. ... View more

LOG ... View more

Dear R9,   I changed the code as you suggested (here below) /* 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; proc sql; create table num_ourliers as select case when &VAR>Variable_max then 1 else 0 end as n_over, case when &VAR<Variable_min then 1 else 0 end as n_under But then I got the following error: proc sql; 2 + create table num_ourliers as select case when 3 + ALTERNATIVEBEGIN>Variable_max then 1 else 0 end as n_over, case when ALTERNATIVEBEGIN<Variable_min then 1 else 0 end as n_under from base ; ERROR: The following columns were not found in the contributing tables: ALTERNATIVEBEGIN. 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; %mend macro_outliers;   additional clarification, the variable var works in two ways: 1. search the "variable" as an observation of the variable "variable_name" which is the output of a proc content enhance with some values, in this case the relevant are variable_max and variable_min table example variable_name lenght table ... variable_min variable_max ALTERNATIVEBEGIN ................ 01.01.2015 31.12.2015 gender ..... .............................. - - age ........................................ 18 40   2. search in a table where the variable is really a variable table example: id ALTERNATIVEBEGIN gender age x1 01.01.2014 m 25 x2 02.02.2015 m 50   I would the macro to generate the following output, as enhancement in the table described at variable_name lenght table ... variable_min variable_max outliers_up outliers_down outliers_up ALTERNATIVEBEGIN ................ 01.01.2015 31.12.2015 1 0  gender ..... .............................. - - - - age ........................................ 18 40 0 1   Attached you find a screenshot of the output (I got something but it does nto look meaningful) and in the next post the log I got. Thanks a lot guys! ... View more

Hi Tom,   thanks a lot for your explanations, now I am getting abetter understanding. Coming back to my specific case:   %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; proc sql; create table num_ourliers as select case when "&VAR">Variable_max then 1 else 0 end as n_over, case when "&VAR"<Variable_min then 1 else 0 end 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; %mend macro_outliers;   I underlined the critical part, that I would like to change including the following steps: 1. if the variable is numeric then execute the macro 2. if the variable is not numeric, try to convert it to numeric and execute the macro 3. if the variable cannot be converted to numeric, well do not du anything but please let the code go further. How can I efficiently embed these 3 steps in my code? Thanks.     ... View more

average between male and female is not the point, not even an issue, quite easy to calculate: if you consider it as bolean variable (0 or 1... conversion to numbers!!!) you could easily calculate the average sex as the percentage of males/females in the whole population. You know... the advantages of converting to numbers and playing with them. If I ask you which is the mean of 10 and 20... the answer can be: to calculate the mean you should have numbers? Is it a joke? They simpy are numbers... why should I explain it to sas? I would understand the other way around: to force numbers to be considered as charachters but it is a quite weird transformation. I do not blame SAS, of course is a quite close universe and not so flexible (at least in comparison to apache spark or similar) but it does a good job, that's why I am using it and I am trying to get the most out of it. This forum is also really helpful. ... View more

Hi Tom,   do you see any macro in this post? I just mentioned as backgroun. Well basically everything that I am doing is based on the proc content. The problem is that I cannot define a specific rule for each variable, I am trying to automatize the process. 1) are you sure that missing work also for not numeric variables? proc means data=in.test STACKODS n nmiss range min max; var _numeric_ ; ods output summary=DC.stacked ;run; the results that i get from the step above are produced only for numeric variables. 2) I did not about cats, but thanks to the explanation of Rheinard... it seems to be really helpful 3) good to know... I am trying to figure out if it can help me.   If you are referring to the other post... well thanks to the brilliant help of Rheinard... I am getting where I want. If you also can effectively support, you are welcome. 😉 Thanks. ... View more

1) maybe I was not clear enough: if a variable (colum) contains just one observation that cannot be anyhow numeric (e.g. o0001) all the observations should be then numeric. I am not expecting different treatments for observations belonging to the same variable. 2) Again: my aim would be to prioritize the categorization (e.g. if a variable contains only numbers...) I expect to have it automatically as number. Or at least in the same straighforward way as r, matlab or Python do.  3) Yes I would like to sidestep the responsability, but I cannot apparently. If I use some libraries in Python (I ignore 90% of what is it behind) but I get the data I need for my purpose. My aim is not to be a sas consultant, I just need sas to produce what I want in the easiest, quickiest and flexible way. Anyway thanks for the explanation... it seem than we could invest our team in more productive ways than keep discussing on it. ... View more

I am telling you to use a variable as a number when it can be treated as a number. If you have a value abcd or o0001... of course it cannot be used as a number. When I import the data from a csv or an excel (e.g 300 variables)... should I specify for each column how to read the value? If there is no logic to say to SAS: "Dear friend SAS... well I am doing calculations, please consider numbers as numbers. I do not need your support to write a poem"... then I should stick to this conversion logic. Then how can I write? convert all the variables of the table to numeric, if possible. Variables containing abcd or o0002 cannot of course. ... View more

in case of: if "barracuda" > 1 then... well in this case I would expect an error. ... View more

if a variable is 0001 or 201602... I would like to deal with them as numbers, independently by the variable type definition of SAS. If I check 201602>201601 I expect a meaningful output, without converting them to a numeric in advance. I do not expect something like in Python where you can even compare with < and > the letters of he alphabet and much more, but if they are numbers... cannot sas somehow understand it without the need of explaining everything? My aim is to write compacts codes, I would like to avoid to waste time just to learn how SAS is not able to undestand the data and how to help him to understand. Thanks. ... View more