Dear experts,   I am looking fo an effective way (I assume proc compare or proc sql) to compare 2 data sets. The second data set should contain almost the same data of the previou one (some small changes) and additional observations. My aim is to get the complete delta between the two data sets, i.e. the changes to the existing data and the new ones. Any suggestion? Thanks a lot, SH. ... View more

Dear experts, I was searching a variable starting with "_", eg _xxxx in one excel table. Now I am performing a join and I am looking the same variable specyfind the table, e.g. t1._xxxx but I got the following message: column _xxxx could not be found. Any idea to solve it? Thanks a lot.   ... View more

Dear experts,   I already tested the following script:   /* check 03: count missing */ proc sql; create table base_missing as select VPS_AUFENTHALTSLAND, case when VPS_AUFENTHALTSLAND in ('',' ','-','.') then 1 else 0 end as missing, case when VPS_AUFENTHALTSLAND ='NB' then 1 else 0 end as default_value from in.test ;run; proc sql; create table base_missing as select sum(missing) as num_missing, sum(default_value) as num_def_value from base_missing ;run; proc sql; update DC.input_analysis_res set number_missing_calc = (select num_missing FROM base_missing) WHERE Variable_Name_Original='VPS_AUFENTHALTSLAND' ;run; proc sql; update DC.input_analysis_res set number_def_value = (select num_def_value FROM base_missing) WHERE Variable_Name_Original='VPS_AUFENTHALTSLAND' ;run;   and it works correctly. Now I would like to call it in a macro:   step1: %macro macro_missing (var=,def_val=); proc sql; create table base_missing as select "&VAR", case when "&VAR" in ('',' ','-','.') then 1 else 0 end as missing, case when "&VAR" ="&def_val" then 1 else 0 end as default_value from in.test ;run; proc sql; create table base_missing as select sum(missing) as num_missing,sum(default_value) as num_def_value from base_missing ;run; proc sql; update DC.input_analysis_res set number_missing_calc = (select num_missing FROM base_missing) WHERE Variable_Name_Original="&VAR" ;run; proc sql; update DC.input_analysis_res set number_def_value = (select num_def_value FROM base_missing) WHERE Variable_Name_Original="&VAR" ;run; %mend macro_missing;   step 2:   data _null_; set DC.input_analysis_res; if Variable='VPS_AUFENTHALTSLAND'; call execute('%macro_missing (var='||strip(variable_name)||'def_val='||strip(Default_value)||');'); run;   I get neither an error message nor the expected results. Do someone have a suggestion? I do not see any error. Thanks in advance. ... View more

Dear experts,   I am trying to debug a macro but I do not understand the error, attached the log. Does anyone of you understand it? I limited the macro to just one case (if Variable_value_list ne "" then */ --- replaced by ---> if variable_name='VPS_AUFENTHALTSLAND' then) and the case standalone (running it outside the macro) wrok.     options mlogic SYMBOLGEN; %macro In_list (var=,vals=); /* check */ proc sql; create table values_found as select distinct &VAR from in.test group by 1; run; proc sql; create table &vals as select distinct variable_&vals from DC.input_analysis_res where variable_name='&VAR' ; run; proc sql; create table out_of_&vals as select distinct a.*, b.* from values_found a, &vals b ; run; proc sql; create table out_of_&vals as select &VAR from out_of_&vals where Variable_&vals not contains &VAR ; run; proc sql; create table out_of_&vals as select b.&VAR, count(*) as _n_ from out_of_&vals a, in.test b where a.&VAR=b.&VAR group by 1 ; run; proc sql; create table number_unexpected_values as select sum(_n_) as number_unexpected_values from out_of_&vals; run; data unexpected_values_list (drop=&VAR rename=(tmp=&VAR)); length tmp $2000; set out_of_&vals end=last; retain tmp; tmp=catx(",",tmp,&VAR); if last then output ; run; proc sql; update DC.input_analysis_res set number_unexpected_value = (select number_unexpected_values FROM number_unexpected_values) WHERE Variable_Name_Original='&VAR' ; run; proc sql; update DC.input_analysis_res set Unexpected_values = (select &VAR FROM unexpected_values_list) WHERE Variable_Name_Original='&VAR' ; run; %mend In_List;     data _null_; set DC.input_analysis_res; /* check 01 if Variable_value_list ne "" then */ if variable_name='VPS_AUFENTHALTSLAND' then call execute('%In_List (var='||strip(variable_name)||',vals='||strip(variable_value_list)||');'); run;     ... View more

Dear experts,   let's assume that I have a adata set containing the following variables: name, surname, age, address, salary201401, salary201402, salary201403... salary201512   I qould like to use a proc sql or a data step to get the following variables: - name - all the salaries (i.e. variables contains "salary")   Any suggestion?   Thanks a lot       ... View more

Dear Experts,    I am in the following situation (attached file):   I have several produts (row) and and different variables corresponding to different monthly date, e.g. XX_201501, XX_201502, XX_201503... XX_201610 (progressiv date). How can I sum by row from 201503 to 201506? Thanks and best regards.      ... View more

Dear Experts,   let's assume there is the following data set with one variable (country) and severa observations.   country BU JA US … KR   How can I create a data set where I still have the variable but all the observations are compressed in one?   Expected output: County BU, JA, US,...,KR ... View more