Is there better way than this %macro optimizing_var_len(__dataset=,label_refine=0); %if "&__dataset"^="" and %sysfunc(exist(&__dataset))=1 %then %do; libname temp111 "/SAS_Shared_Data/DATA_DUMPS/optimizing_var_len"; data _null_; if index("&__dataset",'.') then do; call symputx('__lib',scan("&__dataset",1,'.'),'l'); call symputx('__dataset',scan("&__dataset",2,'.'),'l'); end; else call symputx('__lib','work','l'); run; %put &__lib &__dataset; proc contents noprint data=&__lib..&__dataset out=__col; run; proc sql noprint; select cat("max(length(trim(",name,"))) as ",name) into : __char SEPARATED by "," from __col where type=2; select count(name) into : __char_cnt SEPARATED by "," from __col where type=2; select count(name) into : __num_cnt from __col where type=1; quit; %if &__num_cnt>0 %then %do; proc means data=&__lib..&__dataset min max sum stackods ; ods output summary=__mean ; run; %end; %else %do; data __mean; length variable $8 min max sum 3; delete; run; %end; data mean_slect3 mean_slect4; set __mean; if max(abs(min),abs(max))<=100 and int(sum)=sum then output mean_slect3; else if max(abs(min),abs(max))<=100000 and int(sum)=sum then output mean_slect4; run; %if &__char_cnt>0 %then %do; proc sql; create table o as select &__char from &__lib..&__dataset; quit; proc transpose data=o out=o_t ; run; %end; %else %do; data o_t; length _name_ $10 col1 3; delete; run; %end; %DEL_VAR1: %let __char_len=; %let _len_3=; %let _len_4=; proc sql noprint; select cat(_name_," $",strip(put(col1,best10.))) into: __char_len SEPARATED by " " from o_t; select cat(Variable," 3") into : _len_3 SEPARATED by " " from mean_slect3; select cat(Variable," 4") into : _len_4 SEPARATED by " " from mean_slect4; quit; options varlenchk=nowarn; data temp111.&__dataset ; %if "&__char_len"^="" %then length &__char_len;; set &__lib..&__dataset; %if "&_len_3"^="" or "&_len_4"^="" %then length &_len_3 &_len_4 ;; run; options varlenchk=warn; proc compare base=&__lib..&__dataset compare=temp111.&__dataset outstat=outcompstat(where=(_TYPE_='NDIF' )) nosummary novalues ; ods output CompareSummary(nowarn)=compare_summ ; run; proc sql noprint; select count(1) into: __unequal_var from outcompstat where _BASE_^=0 or _comp_^=0; select count(1) into: __equal_var from outcompstat where _BASE_=0 and _comp_=0; quit; %if &__unequal_var>0 %then %do; proc sql noprint; delete * from mean_slect3 where variable in (select _var_ from outcompstat where _BASE_^=0 or _comp_^=0); delete * from mean_slect4 where variable in (select _var_ from outcompstat _BASE_^=0 or _comp_^=0); quit; %goto del_var1; %end; %else %if &__equal_var=&__num_cnt and %sysfunc(exist(compare_summ))=0 %then %do; proc copy in=temp111 out=&__lib. move ; select &__dataset; run; %end; %if &label_refine=1 %then %do; data _null_; set __col end=last; if _n_=1 then call execute('proc datasets library=&__lib nolist noprint; modify &__dataset ;'); call execute(cat('attrib ',strip(name),' label="',upcase(strip(name)),'";')); if last then call execute(';quit;run;'); run; %end; proc sql; drop table __col, o, o_t, mean_slect3 ,mean_slect4,outcompstat,__mean %if %sysfunc(exist(compare_summ))=1 %then ,compare_summ; %if %sysfunc(exist(temp111.&__dataset))=1 %then ,temp111.&__dataset; ;quit; libname temp111 clear; %end; %else %put WARNING: Invalide dataset &__dataset; %mend optimizing_var_len; %optimizing_var_len(__dataset=j);
... View more
