output from iteration

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 14
Accepted Solution

output from iteration

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.


Accepted Solutions
Solution
‎07-19-2017 09:59 AM
Respected Advisor
Posts: 3,896

Re: output from iteration

@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;

View solution in original post


All Replies
Super User
Super User
Posts: 7,408

Re: output from iteration

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.

Solution
‎07-19-2017 09:59 AM
Respected Advisor
Posts: 3,896

Re: output from iteration

@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;

Occasional Contributor
Posts: 14

Re: output from iteration

Thank you @Patrick, your macro is better Smiley Happy

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 179 views
  • 2 likes
  • 3 in conversation