BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Jedrzej
Obsidian | Level 7

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Patrick
Opal | Level 21

@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

3 REPLIES 3
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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
Opal | Level 21

@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
Obsidian | Level 7

Thank you @Patrick, your macro is better 🙂

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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