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.     ... View more

Hi Everyone, I would like to create a macro which could compare all the dates of my database. The idea is to create beforehand an unique dataset grouping all the dates of all the database in the chronological order. For example, create a dataset with one line per subject grouping the date of birth, the date of consent, the date of follow-up and the date of end of study. All my dates are in format DDMMYYYY10. Subject_id Date_of_birth Date_of_consent Date_of_follow-up Date_of_end_of_study 1 xx/xx/xxxx xx/xx/xxxx xx/xx/xxxx xx/xx/xxxx 2 xx/xx/xxxx xx/xx/xxxx xx/xx/xxxx xx/xx/xxxx 3 xx/xx/xxxx xx/xx/xxxx xx/xx/xxxx xx/xx/xxxx My idea is to create a macro which would compare all the dates and detect all the inconsitencies in one instruction. In this example, the macro would compare the date of birth to the date of consent, then to the date of follow-up, and then to the end of study. If one of this date is < to the date of birth, create a colomn "querie" in which is specified "Inconsistencies detected between dates". Then do the same thing with the date of consent, compare it to the date of follow-up and then to the date of end of study and if one of this date is < to date of consent, specify in the column querie "Inconsistencies detected between dates". Is there someone who has already made this or who could help me? Thank you very much. ... View more