MACRO FOR FORMAT TRANSFORMATION

[Reeza · Accepted solution]

data XXX.%scan(&liste,&i);

set XXX.%scan(&liste,&i);

format %substr(%scan(&&var_list&i,&j),1,&pos2) %scan(&&var_list&i,&j). ;

put _all_ ;

drop %scan(&&var_list&i,&j);

 

run;

%end;

The above step where you apply the formats is problematic because you're essentially reading and recreating the data set. Instead use PROC DATASETS to update the formats without recreating the data set. That will save you a significant amount of time.

If it still takes too long, run it a few times with msglevel=i and look at the timing to see which step is taking the longest and then work on improving/reducing the speed of that step.

---

@sumsar wrote:

Hi everyone, 

I have created a macro which permits to modify the format of all data in all the datasets of my librairy at once, in the way you can see in the file attached.

My macro works well, but it takes a very long time to process (sometimes up to 10 minutes!).

Could you try to find a way to optimize it please? You'll find the code below :

*We put all the datasets in a list and we get the number of datasets;

proc sql;

select distinct memname into :liste separated by " " from sashelp.vcolumn where libname="XXX ";

select count (distinct memname) into :nb from sashelp.vcolumn where libname="XXX";

*We replace the var column by the value of the var_decode column and we delete the var_decode column;

option FMTSEARCH=(XXX.formats);

%macro content;

%do i=1 %to &nb;

proc contents data=XXX.%scan(&liste,&i)  out=contents&i noprint;run ;

data data2&i ;

length name $32. ;

set contents&i (keep=name rename=(name=label));

where index (upcase(label), '_DECODE') gt 0 ;

name=substr (label, 1, index (upcase(label), '_DECODE') -1) ;

run ;

proc sort data=contents&i; by name; run;

proc sort data=data2&i; by name; run;

data data3&i ;

merge contents&i (in=A) data2&i (in=B);

by name ;

if B;

run ;

/* We create formats for numeric data*/

data _null_ ;

  set data3&i (where=(type=1) ) ;

  call execute (

"proc sort data=XXX.%scan(&liste,&i) out=data5&i (keep=" !! name !! " " !! label !! ") nodupkey;

      by " !! label !! " ;

run ;

data data6&i ( drop= " !! name !! " rename=(" !! label !!"=label));

      retain type 'n' fmtname " !! quote (label) !! ";

      retain start 0 end 0 ;

        set data5&i ;

        by " !! label !! " ;

        if first." !! label !! " then start=" !! name !! ";

        if last." !! label !! " then do ;

           end=" !! name !! " ;

             output ;

        end ;

run ;

Proc format library=XXX.formats cntlin=data6&i ;

run ; ");

run ;

proc sql;

select name into :var_list&i separated by " " from contents&i where index (upcase(name), '_DECODE') gt 0;

select count(*) into :nbr&i from contents&i where index (upcase(name), '_DECODE') gt 0;

quit;

%put &&var_list&i;

%put &&nbr&i;

%do j=1 %to &&nbr&i;

%let pos1= %index(%scan(&&var_list&i,&j),_decode);

%let pos2=%eval(&pos1 - 1);

data XXX.%scan(&liste,&i);

set XXX.%scan(&liste,&i);

format %substr(%scan(&&var_list&i,&j),1,&pos2) %scan(&&var_list&i,&j). ;

put _all_ ;

drop %scan(&&var_list&i,&j);

run;

%end;

%end;

%mend;

%content;

Thanks in advance.

---

[sumsar]

Hi Reeza,

Thank you so much. By replacing the data/set steps by proc datasets make win a lot of time (I win 9 minutes in total and my macro took11 minutes to process before this modificaion).

Best regards.