SAS-如何找出数据集超长变量及观测，并自动进行变量的拆分...

%macro  aut_dev_var(inds=,maxlen=%str(200),cnt=%str( ));
%local libname memname;
%if &inds.= %then %do;
   %put note:plesce check your dataset name;
   %return;
%end;
%if %length(%sysfunc(compress("&inds.","."))) ne %length(%sysfunc(compress("&inds.",""))) %then %do;
   %let libname=%scan("&inds.",1,".");
   %let memname=%scan("&inds.",2,".");
%end;
%else %do;
   %let libname=work;
   %let memname=&inds.;
%end;

/*情况一：inds 输入为单个数据集 */

proc contents data=&libname..&memname. out=_varstemp10(keep=memname name label varnum type length ) directory noprint memtype=data centiles;
proc sort data=_varstemp10  out=_varstemp10  sortseq=linguistic(numeric_collation=on);by memname varnum ;
quit;

/*找出需要转置处理的变量 */
data _varstemp11;
  set _varstemp10;
  if input(&maxlen.,??best.)<length and type=2  ;
run;
%let dsid=%sysfunc(open(_varstemp11));
%let nobs=%sysfunc(attrn(&dsid,nobs));
%let rc= %sysfunc(close(&dsid));

%if  &nobs. eq 0 %then %do;
%put ******************************************************************************;
skip  2;
%put &memname.不存在超过限定长度的变量以及观测...将跳出后续处理;
skip  2;
%put ******************************************************************************;
%goto exit;
%end;

proc sql noprint;
    select name into:translist separated by " " from  _varstemp11   ;
  quit;

/*衍生数据集行号:作为索引变量，数据集转置key变量*/
data _varstemp17;
  set &libname..&memname.;
  temp_line_id=_n_;
run;
proc transpose data=_varstemp17 out=_varstemp17  prefix=value;
by  temp_line_id;
var &translist.;
run;
/*进行筛选，将超过指定长度的观测挑选出来*/
data _varstemp17;
  length  domain value1 $3000.;
  set _varstemp17;
  length=length(value1);
  domain=compress(%upcase("&memname."));
  if input(&maxlen.,??best.)<length;
run;


/*获取超出指定长度的记录数*/
%let dsid=%sysfunc(open(_varstemp17));
%let nobs=%sysfunc(attrn(&dsid,nobs));
%let rc= %sysfunc(close(&dsid));


%if  &nobs. eq 0 %then %do;
%put ******************************************************************************;
skip  2;
%put &memname.不存在超过限定长度的变量以及观测...将跳出后续处理;
skip 2;
%put ******************************************************************************;
%goto exit;
%end;

/*找出数据集中变量超过指定长度的变量的最长字符个数*/
proc sql undo_policy=none;
create table _varstemp16 as  select distinct domain,_name_,_label_,max(length) as max  from   _varstemp17
group by domain,_name_;
quit;

data _varstemp12;
  set _varstemp16;
  do i=1 to (max*2)/(&maxlen.)+1;
  newvar=compress(strip(_name_)||strip("_&cnt.")||strip(i)||strip("=ksubstr(strip(")||strip(_name_)||strip("),")||ifn(i=1,1,(i-1)*100+1)||strip(",100)"));
  newlabl=compress(strip(_name_)||strip("_&cnt.")||strip(i)||strip('="')||strip(_label_)||strip(i)||strip('"'));
  output;
  end;
run;

proc sort data=_varstemp12(rename=(domain=memname _name_=name))  out=_varstemp13  sortseq=linguistic(numeric_collation=on);by  memname name i;quit;
proc sort data=_varstemp10  out=_varstemp10  sortseq=linguistic(numeric_collation=on);by  memname name varnum ;quit;

data _varstemp14;
  length memname name $3000.;
  merge _varstemp10 _varstemp13;
  by memname name;
  if ^missing(i) then name=compress(strip(name)||strip("_&cnt.")||strip(i));
run;

proc sql noprint;
    select newvar into:new_varlist separated by ";" from _varstemp12   ;
    select newlabl into:new_lablist separated by "  " from _varstemp12   ;
    select name into:new_ordlist separated by "  " from _varstemp14  order by varnum,i ;
    select name into:new_lenlist separated by "  " from _varstemp14(where=(^missing(i)));
  select  strip("max(length(")||strip(name)||strip("))")||"  as  "||strip("len_")||strip(name) into:droplist separated by ","
  from _varstemp14(where=(^missing(i)))  ;
    select NAME into:chg_lenlist separated by " " from _varstemp14(where=(type=2 AND length>200 ));
quit;

 /*
option  error 关闭黑色的数据error 主要由于变量进行ksubstr进行截取的时候出现位置问题.这里可以关闭，不显示
同样varlenchk=nowarn 也是关闭警告，在变量长度发生改变的时候关闭警告
*/
  option error=0 varlenchk=nowarn;

data &memname.;
  set &libname..&memname.;
  length &new_lenlist. $200.;format &new_lenlist. $200.;informat &new_lenlist. $200.;
  &new_varlist.;;
  label &new_lablist.;
run;

data &memname.;
  length &chg_lenlist. $200.;
  set  &memname.;
run;

data &memname.(keep=&new_ordlist.);
  retain &new_ordlist.;
  set  &memname.;
run;

proc sql undo_policy=none;
create table _varstemp15 as select distinct &droplist. from  &memname.  ;
quit;


proc transpose data=_varstemp15 out=_varstemp18 ;
var _all_;
run;


data _varstemp18;
  set _varstemp18;
  length  domain var $200.;
  domain=upcase("&memname.");
  var=substr(_name_,5);
  drop _name_;
  if col1 in (0 1);
run;

/*获取多余变量名称*/
%let dsid=%sysfunc(open(_varstemp18));
%let nobss=%sysfunc(attrn(&dsid,nobs));
%let rc= %sysfunc(close(&dsid));


%if  &nobss. eq 0 %then %do;
%put ******************************************************************************;
skip  2;
%put &memname.不存在多余变量...程序将跳出...
skip 2;
%put ******************************************************************************;
%goto  exit;
%end;


  proc sql noprint;
      select var into:dp_list separated by " " from _varstemp18   ;
  quit;

data &memname.(drop=&dp_list.);
  set &memname.;
run;

/*删除过程中的衍生数据集*/
%symdel droplista/nowarn;
%global droplista;
proc contents data=work._all_ out=_varstemp10(where=(index(memname,"_VARSTEMP")) keep=memname) directory noprint memtype=data centiles;
proc sort data=_varstemp10  out=_varstemp10  sortseq=linguistic(numeric_collation=on) nodupkey;by memname ;
run;
proc sql noprint;
select  distinct memname into:droplista separated by " " from _varstemp10;
quit;

proc delete data= &droplista  ;quit;
%mend;