output from iteration

[Jedrzej]

Hi,

I have a macro which trims variables in the data set to the length of a string.

now i have in one library about 20 sets that i want to pass through this macro.

I've created a data set (name of set "tab_par") with names of these sets etc. it looks like this:

set:

----

set1

set2

set3

set4

....

%macro string;

　

proc sql;

select count(*) into :rows from work.tab_par noprint;

quit;

%do i = 1 %to &rows %by 1;

data _null_;

set tab_par ;

if _n_=&i;

call symputx('file',set);

run;

　

　

%let indsn=abc.&file;

%let outdsn=work.&file;

%let dsid=%sysfunc(open(&indsn,i));

%let nvars=%sysfunc(attrn(&dsid,nvars));

%let nobs=%sysfunc(attrn(&dsid,nobs));

%let label=%sysfunc(attrc(&dsid,label));

%let rc=%sysfunc(close(&dsid));

%if %length(&label)>0 %then %let label= label=&label;

%macro help(num);

%global var type len;

%let dsid=%sysfunc(open(&indsn,i));

%let var=%sysfunc(varname(&dsid,&num));

%let type=%sysfunc(vartype(&dsid,&num));

%let len=%sysfunc(varlen(&dsid,&num));

%let rc=%sysfunc(close(&dsid));

%mend help;

options varlenchk=nowarn;

%if &nobs>0 %then %do;

data temp(compress=Y);

set &indsn end=last;

retain _1-_&nvars 1;

length _all $10000;

%do i=1 %to &nvars;

%help(&i);

%if &type=C %then _&i=max(_&i,length(&var));

%else if _n_=1 then _&i=&len; ;

%end;

if last then do;

%do i=1 %to &nvars;

output;

%help(&i); _all=cat(strip(_all)," &var ", %if &type=C %then '$',; strip(put(_&i,best.))); %end;

call execute("data &outdsn(&label); length "||strip(_all)|| '; set temp; run;'); drop _: ; end; run; %end;

%else %do; data &outdsn(&label); set &indsn; run;

%end;

options varlenchk=warn;

%end;

　

%mend string;

indsn infiles the file from the library abc

outdsn outputs trimmed dataset to work.

Now, my problem is that only the first data set "set1" outputs and I want all sets to be outputed.

probably i've missed something obvious but I can't find it.

thank you in advance.

[RW9]

Sorry, that code is very hard to read.  First, why do you need to trim the length, if you put compress on in the data step then the size of the file is automatically stripped down, no need to hard code lengths down.

http://support.sas.com/documentation/cdl/en/lrdict/64316/HTML/default/viewer.htm#a001288760.htm

However if you insist on doing it yourself, then break it down into two logical steps:

/* Create some test data */
data class;
  set sashelp.class;
run;
data cars;
  set sashelp.cars;
run;

/* Create empty table */
data meta;
  length libname memname name $32 length 8;
  if _n_ < 0;
run;

/* Get lengths from max */
data _null_;
  set sashelp.vcolumn (where=(libname="WORK" and memname in ("CLASS","CARS") and type="char"));
  call execute('proc sql;
                  insert into META
                  set LIBNAME="'||strip(libname)||'",
                      MEMNAME="'||strip(memname)||'",
                      NAME="'||strip(name)||'",
                      LENGTH=(select max(length('||strip(name)||')) from '||catx('.',libname,memname)||');
                quit;');
run;

/* Set new lengths */
data _null_;
  set meta;
  by libname memname;
  if first.memname then call execute('proc sql; alter table '||catx('.',libname,memname)||' ');
  call execute(' modify '||strip(name)||' char('||strip(put(length,best.))||')');
  if last.memname then call execute(';quit;');
run;

I would also avoid using SAS keywords - set - as dataset names as that is hugely confusing.

[Patrick · Accepted solution]

@Jedrzej

Your macro got too much macro for my taste. What about something as below:

%macro reduce_varlen(ds);
  data _null_;
    set &ds end=last;
    array charVars {*} _character_;
    if _n_=1 then
      do;
        length varname $32 maxLen 8;
        dcl hash h1();
        h1.defineKey('varname');
        h1.defineData('varname','maxLen');
        h1.defineDone();
      end;
    do i=1 to dim(charVars);
      call missing(maxLen);
      varname=vname(charVars[i]);
      rc=h1.find();
      maxLen=max(maxLen,length(charVars[i]));
      rc=h1.ref();
    end;
    if last then h1.output(dataset:'maxlengths');
  run;

  filename codegen temp;
  data _null_;
  /*  file print;*/
    file codegen;
    set maxlengths end=last;
    if _n_=1 then
      do;
        put 'proc sql noprint;';
        put "  alter table &ds";
        put '    modify';
        put '      ' varname 'char(' maxLen +(-1) ')';
      end;
    else
      do;
        put '     ,' varname 'char(' maxLen +(-1) ')';
      end;
    if last then
      do;
        put '  ;';
        put 'quit;';
      end;
  run;

  %include codegen / source2;
  filename codegen clear;
%mend;

/* create sample data in WORK */
proc copy in=sashelp out=work memtype=data;
run;quit;

/* create list of work tables */
proc sql;
  create table dirlist as
    select libname, memname
    from dictionary.tables
    where libname='WORK' and memtype='DATA'
    ;
quit;

/* for all tables in dirlist: */
/* change length of character variables to max string lenght in these char variables */
data _null_;
  set dirlist;
  call execute(cats('%reduce_varlen(',libname,'.',memname,')'));
run;

[Jedrzej]

Thank you @Patrick, your macro is better 🙂