Hi @qoit    Thank you for the interesting discussion, and if my understanding is proper, you concatenate all the files together after setting the BDATE in all datasets in this folder (all files in a library) identically regarding format. I hope that I get your idea correctly ?   Warmest regards. ... View more

Hi @qoit  Thank you very much for your helps so far. Before looking at the code, my idea is that we cannot concatenate the file when the "type" of BDATE are inconsistent among datasets, right? Because in our example, BDATE in Zambia is Numeric while it is a character in Argentina.   Please correct me if I fell into any fallacy. Warmest regards. ... View more

Hi @qoit    Thank you for your reply, I add my two files in this folder here, could you please have a look.   Many thanks and warmest regards.   ... View more

Hi @qoit  Thank you for your reply, The code now is options mprint; libname test "C:\Users\pnguyen\Desktop\merge2" access=readonly; proc contents data=test._ALL_ out=contents; run; /*I just change to one country for printing log purpose*/ /*******DROP BDATE**************/ %macro changedate(lib,dsname,var2del); data _null_; if 0 then set &lib..&dsname.;/*changed this line*/ call symputx('bdate_type',vtype(&var2del)); stop; run; data work.&dsname.; set &lib..&dsname.;/*changed this line*/ %if &bdate_type=C %then %do; if length(&var2del)=5 and upcase(&var2del) ne 'NA' then SASDATE = input(&var2del,32.)+'30DEC1899'd ; else SASDATE= input(&var2del,anydtdte.); %end; %else %do; SASDATE=&var2del; %end; format SASDATE yymmdd10.; if not missing(sasdate) then fir_age = log(1+(2020-year(SASDATE))); run; /****macro for BDATE****************/ %mend; data _null_; set sashelp.vtable(where=(libname='TEST')); cmd = catx(',', '%changedate('|| strip(libname), strip(memname), 'BDATE'||');'); put cmd=; call execute(cmd); put _N_= memname= ' was submitted to run' ; run; And the log now is 34 /*I just change to one country for printing log purpose*/ 35 36 /*******DROP BDATE**************/ 37 %macro changedate(lib,dsname,var2del); 38 data _null_; 39 if 0 then set &lib..&dsname.; 40 call symputx('bdate_type',vtype(&var2del)); 41 stop; 42 run; 43 44 data work.&dsname.; 45 set &lib..&dsname.; 46 %if &bdate_type=C %then %do; 2 The SAS System 11:34 Monday, February 15, 2021 47 if length(&var2del)=5 and upcase(&var2del) ne 'NA' then 48 SASDATE = input(&var2del,32.)+'30DEC1899'd ; 49 else SASDATE= input(&var2del,anydtdte.); 50 %end; 51 %else %do; 52 SASDATE=&var2del; 53 %end; 54 format SASDATE yymmdd10.; 55 if not missing(sasdate) then fir_age = log(1+(2020-year(SASDATE))); 56 run; 57 58 /****macro for BDATE****************/ 59 60 %mend; 61 62 data _null_; 63 set sashelp.vtable(where=(libname='TEST')); 64 cmd = catx(',', '%changedate('|| strip(libname), strip(memname), 'BDATE'||');'); put cmd=; 65 call execute(cmd); 66 put _N_= memname= ' was submitted to run' ; 67 run; cmd=%changedate(TEST,ARGENTINA_MERGE2,BDATE); MPRINT(CHANGEDATE): data _null_; MPRINT(CHANGEDATE): if 0 then set TEST.ARGENTINA_MERGE2; MPRINT(CHANGEDATE): call symputx('bdate_type',vtype(BDATE)); MPRINT(CHANGEDATE): stop; MPRINT(CHANGEDATE): run; MPRINT(CHANGEDATE): data work.ARGENTINA_MERGE2; MPRINT(CHANGEDATE): set TEST.ARGENTINA_MERGE2; MPRINT(CHANGEDATE): SASDATE=BDATE; MPRINT(CHANGEDATE): format SASDATE yymmdd10.; MPRINT(CHANGEDATE): if not missing(sasdate) then fir_age = log(1+(2020-year(SASDATE))); MPRINT(CHANGEDATE): run; _N_=1 memname=ARGENTINA_MERGE2 was submitted to run cmd=%changedate(TEST,ZAMBIA_MERGE2,BDATE); MPRINT(CHANGEDATE): data _null_; MPRINT(CHANGEDATE): if 0 then set TEST.ZAMBIA_MERGE2; MPRINT(CHANGEDATE): call symputx('bdate_type',vtype(BDATE)); MPRINT(CHANGEDATE): stop; MPRINT(CHANGEDATE): run; MPRINT(CHANGEDATE): data work.ZAMBIA_MERGE2; MPRINT(CHANGEDATE): set TEST.ZAMBIA_MERGE2; MPRINT(CHANGEDATE): SASDATE=BDATE; MPRINT(CHANGEDATE): format SASDATE yymmdd10.; MPRINT(CHANGEDATE): if not missing(sasdate) then fir_age = log(1+(2020-year(SASDATE))); MPRINT(CHANGEDATE): run; _N_=2 memname=ZAMBIA_MERGE2 was submitted to run NOTE: There were 2 observations read from the data set SASHELP.VTABLE. WHERE libname='TEST'; NOTE: DATA statement used (Total process time): real time 0.00 seconds cpu time 0.00 seconds NOTE: CALL EXECUTE generated line. 1 + data _null_; if 0 then set TEST.ARGENTINA_MERGE2; call symputx('bdate_type',vtype(BDATE)); stop; run; 3 The SAS System 11:34 Monday, February 15, 2021 NOTE: DATA statement used (Total process time): real time 0.00 seconds cpu time 0.01 seconds NOTE: Line generated by the CALL EXECUTE routine. 1 + data work.ARGENTINA_MERGE2; set TEST.ARGENTINA_MERGE2; SASDATE=BDATE; format SASDATE yymmdd10.; if not _________ 484 1 !+missing(sasdate) then fir_age = NOTE 484-185: Format $YYMMDD was not found or could not be loaded. 2 + log(1+(2020-year(SASDATE))); run; NOTE: Character values have been converted to numeric values at the places given by: (Line):(Column). 2:19 NOTE: Invalid argument to function LOG(-33) at line 2 column 2. Type=131566 ENAME=CENTRAL PUERTO B DEAD - DUPL SEE 134645 NAME=CENTRAL PUERTO B DEAD - DUPL SEE 134645 MNEM=NA DSCD=131566 BDATE=34463 CURWS=NA INDC3=UTILS INDM3=NA WC07022=NA WC07023=NA WC07024=NA WC07536=NA WC09802=NA ISIN=NA MAJOR=N ESTAT=DEAD TYPE_1=EQ EXNAME=Buenos Aires SECD=NA ISINID=P LOC=NA RIC=NA GEOGN=ARGENTINA Year=1988 s2=. s3=. s4=. s5=. s6=. s7=. s8=. s9=. s10=. s11=. s12=. s13=. s14=. s15=. s16=. s17=. s18=. s19=. s20=. s21=. s22=. s23=. s24=. s25=. s26=. s27=. s28=. s29=. s30=. s31=. s32=. s33=. s34= s35=224981952135 s36=. s37=. s38=. s39=0.0008752604 s40=6.2118456358 s41=. s42=6 s43=7104.1812008 s44=2.0225056433 s45=1147000000 SASDATE=34463 fir_age=. _ERROR_=1 _N_=1 NOTE: Invalid argument to function LOG(-33) at line 2 column 2. Type=131566 ENAME=CENTRAL PUERTO B DEAD - DUPL SEE 134645 NAME=CENTRAL PUERTO B DEAD - DUPL SEE 134645 MNEM=NA DSCD=131566 BDATE=34463 CURWS=NA INDC3=UTILS INDM3=NA WC07022=NA WC07023=NA WC07024=NA WC07536=NA WC09802=NA ISIN=NA MAJOR=N ESTAT=DEAD TYPE_1=EQ EXNAME=Buenos Aires SECD=NA ISINID=P LOC=NA RIC=NA GEOGN=ARGENTINA Year=1989 s2=. s3=. s4=. s5=. s6=. s7=. s8=. s9=. s10=. s11=. s12=. s13=. s14=. s15=. s16=. s17=. s18=. s19=. s20=. s21=. s22=. s23=. s24=. s25=. s26=. s27=. s28=. s29=. s30=. s31=. s32=. s33=. s34= s35=208880107087 s36=. s37=. s38=. s39=0.0423339608 s40=6.5793887933 s41=. s42=7.3000001907 s43=6497.4255453 s44=4.172755007 s45=1028000000 SASDATE=34463 fir_age=. _ERROR_=1 _N_=2 NOTE: Invalid argument to function LOG(-33) at line 2 column 2. Type=131566 ENAME=CENTRAL PUERTO B DEAD - DUPL SEE 134645 NAME=CENTRAL PUERTO B DEAD - DUPL SEE 134645 MNEM=NA DSCD=131566 BDATE=34463 CURWS=NA INDC3=UTILS INDM3=NA WC07022=NA WC07023=NA WC07024=NA WC07536=NA WC09802=NA ISIN=NA MAJOR=N ESTAT=DEAD TYPE_1=EQ EXNAME=Buenos Aires SECD=NA ISINID=P LOC=NA RIC=NA GEOGN=ARGENTINA Year=1990 s2=. s3=. s4=. s5=. s6=. s7=. s8=. s9=. s10=. s11=. s12=. s13=. s14=. s15=. s16=. s17=. s18=. s19=. s20=. s21=. s22=. s23=. s24=. s25=. s26=. s27=. s28=. s29=. s30=. s31=. s32=. s33=. s34= s35=203726588309 s36=. s37=. s38=. s39=0.4875890833 s40=4.6313224074 s41=. s42=7.0599999428 s43=6245.7085766 s44=2.5576508076 s45=1836000000 SASDATE=34463 fir_age=. _ERROR_=1 _N_=3 NOTE: Invalid argument to function LOG(-33) at line 2 column 2. Type=131566 ENAME=CENTRAL PUERTO B DEAD - DUPL SEE 134645 NAME=CENTRAL PUERTO B DEAD - DUPL SEE 134645 MNEM=NA DSCD=131566 BDATE=34463 CURWS=NA INDC3=UTILS INDM3=NA WC07022=NA WC07023=NA WC07024=NA WC07536=NA WC09802=NA ISIN=NA MAJOR=N ESTAT=DEAD TYPE_1=EQ EXNAME=Buenos Aires SECD=NA ISINID=P LOC=NA RIC=NA GEOGN=ARGENTINA Year=1991 s2=. s3=. s4=. s5=. s6=. s7=. s8=. s9=. s10=. s11=. s12=. s13=. s14=. s15=. s16=. s17=. s18=. s19=. s20=. s21=. s22=. s23=. s24=. s25=. s26=. s27=. s28=. s29=. s30=. s31=. s32=. s33=. s34= s35=222333162872 s36=. s37=. s38=. s39=0.9535544167 s40=6.0780108106 s41=. s42=5.4400000572 s43=6721.2782391 s44=9.8248479578 s45=2439000000 SASDATE=34463 fir_age=. _ERROR_=1 _N_=4 NOTE: Invalid argument to function LOG(-33) at line 2 column 2. Type=131566 ENAME=CENTRAL PUERTO B DEAD - DUPL SEE 134645 NAME=CENTRAL PUERTO B DEAD - DUPL SEE 134645 MNEM=NA DSCD=131566 BDATE=34463 CURWS=NA INDC3=UTILS INDM3=NA WC07022=NA WC07023=NA WC07024=NA WC07536=NA WC09802=NA ISIN=NA MAJOR=N ESTAT=DEAD TYPE_1=EQ EXNAME=Buenos Aires SECD=NA ISINID=P LOC=NA RIC=NA GEOGN=ARGENTINA Year=1992 s2=. s3=. s4=. s5=. s6=. s7=. s8=. s9=. s10=. s11=. s12=. s13=. s14=. s15=. s16=. s17=. s18=. s19=. s20=. s21=. s22=. s23=. s24=. s25=. s26=. s27=. s28=. s29=. s30=. s31=. s32=. s33=. s34= s35=239980394235 s36=. s37=. s38=. s39=0.9906416667 s40=8.1327931143 s41=. s42=6.3600001335 s43=7157.3283112 s44=8.1398717418 s45=4430977523.1 SASDATE=34463 fir_age=. _ERROR_=1 _N_=5 NOTE: Invalid argument to function LOG(-33) at line 2 column 2. Type=131566 ENAME=CENTRAL PUERTO B DEAD - DUPL SEE 134645 NAME=CENTRAL PUERTO B DEAD - DUPL SEE 134645 MNEM=NA DSCD=131566 BDATE=34463 CURWS=NA INDC3=UTILS INDM3=NA WC07022=NA WC07023=NA WC07024=NA WC07536=NA WC09802=NA ISIN=NA MAJOR=N ESTAT=DEAD TYPE_1=EQ EXNAME=Buenos Aires SECD=NA ISINID=P LOC=NA RIC=NA GEOGN=ARGENTINA Year=1993 s2=. s3=. s4=. s5=. s6=. s7=. s8=. s9=. s10=. 4 The SAS System 11:34 Monday, February 15, 2021 s11=. s12=. s13=. s14=. s15=. s16=. s17=. s18=. s19=. s20=. s21=. s22=. s23=. s24=. s25=. s26=. s27=. s28=. s29=. s30=. s31=. s32=. s33=. s34= s35=259675534968 s36=. s37=. s38=. s39=0.9989458333 s40=9.3137999141 s41=. s42=10.100000381 s43=7644.235692 s44=18.609018692 s45=2793085410.1 SASDATE=34463 fir_age=. _ERROR_=1 _N_=6 NOTE: Invalid argument to function LOG(-33) at line 2 column 2. Type=131566 ENAME=CENTRAL PUERTO B DEAD - DUPL SEE 134645 NAME=CENTRAL PUERTO B DEAD - DUPL SEE 134645 MNEM=NA DSCD=131566 BDATE=34463 CURWS=NA INDC3=UTILS INDM3=NA WC07022=NA WC07023=NA WC07024=NA WC07536=NA WC09802=NA ISIN=NA MAJOR=N ESTAT=DEAD TYPE_1=EQ EXNAME=Buenos Aires SECD=NA ISINID=P LOC=NA RIC=NA GEOGN=ARGENTINA Year=1994 s2=. s3=. s4=. s5=. s6=. s7=. s8=. s9=. s10=. s11=. s12=. s13=. s14=. s15=. s16=. s17=. s18=. s19=. s20=. s21=. s22=. s23=. s24=. s25=. s26=. s27=. s28=. s29=. s30=. s31=. s32=. s33=. s34= s35=274830720367 s36=. s37=. s38=. s39=0.9990083333 s40=10.604397141 s41=. s42=11.760000229 s43=7988.6446136 s44=14.320696085 s45=3634931878.3 SASDATE=34463 fir_age=. _ERROR_=1 _N_=7 NOTE: Invalid argument to function LOG(-33) at line 2 column 2. Type=131566 ENAME=CENTRAL PUERTO B DEAD - DUPL SEE 134645 NAME=CENTRAL PUERTO B DEAD - DUPL SEE 134645 MNEM=NA DSCD=131566 BDATE=34463 CURWS=NA INDC3=UTILS INDM3=NA WC07022=NA WC07023=NA WC07024=NA WC07536=NA WC09802=NA ISIN=NA MAJOR=N ESTAT=DEAD TYPE_1=EQ EXNAME=Buenos Aires SECD=NA ISINID=P LOC=NA RIC=NA GEOGN=ARGENTINA Year=1995 s2=. s3=. s4=. s5=. s6=. s7=. s8=. s9=. s10=. s11=. s12=. s13=. s14=. s15=. s16=. s17=. s18=. s19=. s20=. s21=. s22=. s23=. s24=. s25=. s26=. s27=. s28=. s29=. s30=. s31=. s32=. s33=. s34= s35=267011210298 s36=. s37=. s38=. s39=0.99975 s40=10.091012831 s41=. s42=18.799999237 s43=7666.5300042 s44=14.643073963 s45=5609423403.6 SASDATE=34463 fir_age=. _ERROR_=1 _N_=8 NOTE: Invalid argument to function LOG(-33) at line 2 column 2. Type=131566 ENAME=CENTRAL PUERTO B DEAD - DUPL SEE 134645 NAME=CENTRAL PUERTO B DEAD - DUPL SEE 134645 MNEM=NA DSCD=131566 BDATE=34463 CURWS=NA INDC3=UTILS INDM3=NA WC07022=NA WC07023=NA WC07024=NA WC07536=NA WC09802=NA ISIN=NA MAJOR=N ESTAT=DEAD TYPE_1=EQ EXNAME=Buenos Aires SECD=NA ISINID=P LOC=NA RIC=NA GEOGN=ARGENTINA Year=1996 s2=. s3=. s4=. s5=. s6=. s7=. s8=. s9=. s10=. s11=. s12=. s13=. s14=. s15=. s16=. s17=. s18=. s19=. s20=. s21=. s22=. s23=. s24=. s25=. s26=. s27=. s28=. s29=. s30=. s31=. s32=. s33=. s34= s35=281768091695 s36=. s37=. s38=. s39=0.9996625 s40=11.07787165 s41=. s42=17.11000061 s43=7994.2433708 s44=16.42196989 s45=6948536686.6 SASDATE=34463 fir_age=. _ERROR_=1 _N_=9 NOTE: Invalid argument to function LOG(-33) at line 2 column 2. Type=131566 ENAME=CENTRAL PUERTO B DEAD - DUPL SEE 134645 NAME=CENTRAL PUERTO B DEAD - DUPL SEE 134645 MNEM=NA DSCD=131566 BDATE=34463 CURWS=NA INDC3=UTILS INDM3=NA WC07022=NA WC07023=NA WC07024=NA WC07536=NA WC09802=NA ISIN=NA MAJOR=N ESTAT=DEAD TYPE_1=EQ EXNAME=Buenos Aires SECD=NA ISINID=P LOC=NA RIC=NA GEOGN=ARGENTINA Year=1997 s2=. s3=. s4=. s5=. s6=. s7=. s8=. s9=. s10=. s11=. s12=. s13=. s14=. s15=. s16=. s17=. s18=. s19=. s20=. s21=. s22=. s23=. s24=. s25=. s26=. s27=. s28=. s29=. s30=. s31=. s32=. s33=. s34= s35=304622433397 s36=. s37=. s38=. s39=0.9995 s40=12.775328742 s41=. s42=14.819999695 s43=8543.028534 s44=20.232210723 s45=9160272052.4 SASDATE=34463 fir_age=. _ERROR_=1 _N_=10 NOTE: Invalid argument to function LOG(-33) at line 2 column 2. Type=131566 ENAME=CENTRAL PUERTO B DEAD - DUPL SEE 134645 NAME=CENTRAL PUERTO B DEAD - DUPL SEE 134645 MNEM=NA DSCD=131566 BDATE=34463 CURWS=NA INDC3=UTILS INDM3=NA WC07022=NA WC07023=NA WC07024=NA WC07536=NA WC09802=NA ISIN=NA MAJOR=N ESTAT=DEAD TYPE_1=EQ EXNAME=Buenos Aires SECD=NA ISINID=P LOC=NA RIC=NA GEOGN=ARGENTINA Year=1998 s2=. s3=. s4=. s5=. s6=. s7=. s8=. s9=. s10=. s11=. s12=. s13=. s14=. s15=. s16=. s17=. s18=. s19=. s20=. s21=. s22=. s23=. s24=. s25=. s26=. s27=. s28=. s29=. s30=. s31=. s32=. s33=. s34= s35=316350941905 s36=. s37=. s38=. s39=0.9995 s40=12.934446012 s41=. s42=12.649999619 s43=8772.0632096 s44=15.164096127 s45=7290657132.1 SASDATE=34463 fir_age=. _ERROR_=1 _N_=11 NOTE: Invalid argument to function LOG(-33) at line 2 column 2. Type=131566 ENAME=CENTRAL PUERTO B DEAD - DUPL SEE 134645 NAME=CENTRAL PUERTO B DEAD - DUPL SEE 134645 MNEM=NA DSCD=131566 BDATE=34463 CURWS=NA INDC3=UTILS INDM3=NA WC07022=NA WC07023=NA WC07024=NA WC07536=NA WC09802=NA ISIN=NA MAJOR=N ESTAT=DEAD TYPE_1=EQ EXNAME=Buenos Aires SECD=NA ISINID=P LOC=NA RIC=NA GEOGN=ARGENTINA Year=1999 s2=. s3=. s4=. s5=. s6=. s7=. s8=. s9=. s10=. s11=. s12=. s13=. s14=. s15=. s16=. s17=. s18=. s19=. s20=. s21=. s22=. s23=. s24=. s25=. s26=. s27=. s28=. s29=. s30=. s31=. s32=. s33=. s34= s35=305641016669 s36=. s37=. s38=. s39=0.9995 s40=11.555570448 s41=. s42=14.050000191 s43=8381.2539983 s44=19.6977247 s45=23987696390 SASDATE=34463 fir_age=. _ERROR_=1 _N_=12 NOTE: Invalid argument to function LOG(-33) at line 2 column 2. Type=131566 ENAME=CENTRAL PUERTO B DEAD - DUPL SEE 134645 NAME=CENTRAL PUERTO B DEAD - DUPL SEE 134645 MNEM=NA DSCD=131566 BDATE=34463 CURWS=NA INDC3=UTILS INDM3=NA WC07022=NA WC07023=NA WC07024=NA WC07536=NA WC09802=NA ISIN=NA MAJOR=N ESTAT=DEAD TYPE_1=EQ EXNAME=Buenos Aires SECD=NA ISINID=P LOC=NA RIC=NA GEOGN=ARGENTINA Year=2000 s2=. s3=. s4=. s5=. s6=. s7=. s8=. s9=. s10=. s11=. s12=. s13=. s14=. s15=. s16=. s17=. s18=. s19=. s20=. s21=. s22=. s23=. s24=. s25=. s26=. s27=. s28=. s29=. s30=. s31=. s32=. s33=. s34= s35=303229512290 s36=. s37=. s38=. s39=0.9995 s40=11.636069545 s41=. s42=15 s43=8224.1128265 s44=16.129041225 s45=10418314339 SASDATE=34463 fir_age=. _ERROR_=1 _N_=13 NOTE: Invalid argument to function LOG(-33) at line 2 column 2. Type=131566 ENAME=CENTRAL PUERTO B DEAD - DUPL SEE 134645 NAME=CENTRAL PUERTO B DEAD - DUPL SEE 134645 MNEM=NA DSCD=131566 BDATE=34463 CURWS=NA INDC3=UTILS INDM3=NA WC07022=NA WC07023=NA WC07024=NA WC07536=NA WC09802=NA ISIN=NA MAJOR=N ESTAT=DEAD TYPE_1=EQ EXNAME=Buenos Aires SECD=NA ISINID=P LOC=NA RIC=NA GEOGN=ARGENTINA Year=2001 s2=. s3=. s4=. s5=. s6=. s7=. s8=. s9=. s10=. s11=. s12=. s13=. s14=. s15=. s16=. s17=. s18=. s19=. s20=. s21=. s22=. s23=. s24=. s25=. s26=. s27=. s28=. s29=. s30=. s31=. s32=. s33=. s34= s35=289860609223 s36=. s37=. s38=. s39=0.9995 s40=10.273247369 s41=. s42=17.319999695 s43=7776.1378721 s44=12.424415256 5 The SAS System 11:34 Monday, February 15, 2021 s45=2166136829.8 SASDATE=34463 fir_age=. _ERROR_=1 _N_=14 NOTE: Invalid argument to function LOG(-33) at line 2 column 2. Type=131566 ENAME=CENTRAL PUERTO B DEAD - DUPL SEE 134645 NAME=CENTRAL PUERTO B DEAD - DUPL SEE 134645 MNEM=NA DSCD=131566 BDATE=34463 CURWS=NA INDC3=UTILS INDM3=NA WC07022=NA WC07023=NA WC07024=NA WC07536=NA WC09802=NA ISIN=NA MAJOR=N ESTAT=DEAD TYPE_1=EQ EXNAME=Buenos Aires SECD=NA ISINID=P LOC=NA RIC=NA GEOGN=ARGENTINA Year=2002 s2=. s3=. s4=. s5=. s6=. s7=. s8=. s9=. s10=. s11=. s12=. s13=. s14=. s15=. s16=. s17=. s18=. s19=. s20=. s21=. s22=. s23=. s24=. s25=. s26=. s27=. s28=. s29=. s30=. s31=. s32=. s33=. s34= s35=258281789127 s36=. s37=. s38=. s39=3.0632566667 s40=13.370127359 s41=. s42=19.590000153 s43=6854.2940809 s44=16.956939216 s45=2148910000 SASDATE=34463 fir_age=. _ERROR_=1 _N_=15 NOTE: Invalid argument to function LOG(-33) at line 2 column 2. Type=131566 ENAME=CENTRAL PUERTO B DEAD - DUPL SEE 134645 NAME=CENTRAL PUERTO B DEAD - DUPL SEE 134645 MNEM=NA DSCD=131566 BDATE=34463 CURWS=NA INDC3=UTILS INDM3=NA WC07022=NA WC07023=NA WC07024=NA WC07536=NA WC09802=NA ISIN=NA MAJOR=N ESTAT=DEAD TYPE_1=EQ EXNAME=Buenos Aires SECD=NA ISINID=P LOC=NA RIC=NA GEOGN=ARGENTINA Year=2003 s2=. s3=. s4=. s5=. s6=. s7=. s8=. s9=. s10=. s11=. s12=. s13=. s14=. s15=. s16=. s17=. s18=. s19=. s20=. s21=. s22=. s23=. s24=. s25=. s26=. s27=. s28=. s29=. s30=. s31=. s32=. s33=. s34= s35=281106256201 s36=. s37=. s38=. s39=2.9006291667 s40=14.713805105 s41=. s42=15.359999657 s43=7380.4670873 s44=27.428050876 s45=1652010000 SASDATE=34463 fir_age=. _ERROR_=1 _N_=16 NOTE: Invalid argument to function LOG(-33) at line 2 column 2. Type=131566 ENAME=CENTRAL PUERTO B DEAD - DUPL SEE 134645 NAME=CENTRAL PUERTO B DEAD - DUPL SEE 134645 MNEM=NA DSCD=131566 BDATE=34463 CURWS=NA INDC3=UTILS INDM3=NA WC07022=NA WC07023=NA WC07024=NA WC07536=NA WC09802=NA ISIN=NA MAJOR=N ESTAT=DEAD TYPE_1=EQ EXNAME=Buenos Aires SECD=NA ISINID=P LOC=NA RIC=NA GEOGN=ARGENTINA Year=2004 s2=. s3=. s4=. s5=. s6=. s7=. s8=. s9=. s10=. s11=. s12=. s13=. s14=. s15=. s16=. s17=. s18=. s19=. s20=. s21=. s22=. s23=. s24=. s25=. s26=. s27=. s28=. s29=. s30=. s31=. s32=. s33=. s34= s35=306488951657 s36=. s37=. s38=. s39=2.9233008189 s40=16.845026685 s41=. s42=13.522100449 s43=7962.4123092 s44=24.653455736 s45=4124710000 SASDATE=34463 fir_age=. _ERROR_=1 _N_=17 NOTE: Invalid argument to function LOG(-33) at line 2 column 2. Type=131566 ENAME=CENTRAL PUERTO B DEAD - DUPL SEE 134645 NAME=CENTRAL PUERTO B DEAD - DUPL SEE 134645 MNEM=NA DSCD=131566 BDATE=34463 CURWS=NA INDC3=UTILS INDM3=NA WC07022=NA WC07023=NA WC07024=NA WC07536=NA WC09802=NA ISIN=NA MAJOR=N ESTAT=DEAD TYPE_1=EQ EXNAME=Buenos Aires SECD=NA ISINID=P LOC=NA RIC=NA GEOGN=ARGENTINA Year=2005 s2=. s3=. s4=. s5=. s6=. s7=. s8=. s9=. s10=. s11=. s12=. s13=. s14=. s15=. s16=. s17=. s18=. s19=. s20=. s21=. s22=. s23=. s24=. s25=. s26=. s27=. s28=. s29=. s30=. s31=. s32=. s33=. s34= s35=333618311350 s36=. s37=. s38=107.3 s39=2.9036575 s40=17.305394237 s41=. s42=11.505900383 s43=8577.8649943 s44=23.946359887 s45=5265250000 SASDATE=34463 fir_age=. _ERROR_=1 _N_=18 NOTE: Invalid argument to function LOG(-33) at line 2 column 2. Type=131566 ENAME=CENTRAL PUERTO B DEAD - DUPL SEE 134645 NAME=CENTRAL PUERTO B DEAD - DUPL SEE 134645 MNEM=NA DSCD=131566 BDATE=34463 CURWS=NA INDC3=UTILS INDM3=NA WC07022=NA WC07023=NA WC07024=NA WC07536=NA WC09802=NA ISIN=NA MAJOR=N ESTAT=DEAD TYPE_1=EQ EXNAME=Buenos Aires SECD=NA ISINID=P LOC=NA RIC=NA GEOGN=ARGENTINA Year=2006 s2=. s3=. s4=. s5=. s6=. s7=. s8=. s9=. s10=. s11=. s12=. s13=. s14=. s15=. s16=. s17=. s18=. s19=. s20=. s21=. s22=. s23=. s24=. s25=. s26=. s27=. s28=. s29=. s30=. s31=. s32=. s33=. s34= s35=360465082298 s36=. s37=. s38=107.3 s39=3.0543133333 s40=17.406785268 s41=. s42=10.077500343 s43=9174.5024583 s44=22.033339153 s45=5537347786.2 SASDATE=34463 fir_age=. _ERROR_=1 _N_=19 NOTE: Invalid argument to function LOG(-33) at line 2 column 2. WARNING: Limit set by ERRORS= option reached. Further errors of this type will not be printed. Type=131566 ENAME=CENTRAL PUERTO B DEAD - DUPL SEE 134645 NAME=CENTRAL PUERTO B DEAD - DUPL SEE 134645 MNEM=NA DSCD=131566 BDATE=34463 CURWS=NA INDC3=UTILS INDM3=NA WC07022=NA WC07023=NA WC07024=NA WC07536=NA WC09802=NA ISIN=NA MAJOR=N ESTAT=DEAD TYPE_1=EQ EXNAME=Buenos Aires SECD=NA ISINID=P LOC=NA RIC=NA GEOGN=ARGENTINA Year=2007 s2=. s3=. s4=. s5=. s6=. s7=. s8=. s9=. s10=. s11=. s12=. s13=. s14=. s15=. s16=. s17=. s18=. s19=. s20=. s21=. s22=. s23=. s24=. s25=. s26=. s27=. s28=. s29=. s30=. s31=. s32=. s33=. s34= s35=392934518438 s36=. s37=. s38=107.3 s39=3.0956488492 s40=18.282420307 s41=. s42=8.470000267 s43=9901.5118812 s44=19.848384198 s45=6473157762.1 SASDATE=34463 fir_age=. _ERROR_=1 _N_=20 NOTE: Missing values were generated as a result of performing an operation on missing values. Each place is given by: (Number of times) at (Line):(Column). 544 at 2:2 544 at 2:7 544 at 2:13 544 at 2:14 NOTE: Mathematical operations could not be performed at the following places. The results of the operations have been set to missing values. Each place is given by: (Number of times) at (Line):(Column). 13952 at 2:2 NOTE: There were 14496 observations read from the data set TEST.ARGENTINA_MERGE2. NOTE: The data set WORK.ARGENTINA_MERGE2 has 14496 observations and 71 variables. NOTE: DATA statement used (Total process time): real time 0.03 seconds cpu time 0.03 seconds 6 The SAS System 11:34 Monday, February 15, 2021 2 + ; 3 + data _null_; if 0 then set TEST.ZAMBIA_MERGE2; call symputx('bdate_type',vtype(BDATE)); stop; run; NOTE: DATA statement used (Total process time): real time 0.00 seconds cpu time 0.00 seconds 3 + data work.ZAMBIA_MERGE2; set TEST.ZAMBIA_MERGE2; SASDATE=BDATE; format SASDATE yymmdd10.; if not missing(sasdate) then fir_age = log(1+ 4 +(2020-year(SASDATE))); run; NOTE: There were 800 observations read from the data set TEST.ZAMBIA_MERGE2. NOTE: The data set WORK.ZAMBIA_MERGE2 has 800 observations and 71 variables. NOTE: DATA statement used (Total process time): real time 0.01 seconds cpu time 0.00 seconds Thanks and warm regards. ... View more

Hi @SASKiwi  and @qoit    My code now is adjusted a little bit options mprint; libname test "C:\Users\pnguyen\Desktop\merge2" access=readonly; proc contents data=test._ALL_ out=contents; run; /*I just change to one country for printing log purpose*/ /*******DROP BDATE**************/ %macro changedate(lib,dsname,var2del); data _null_; if 0 then set test.&dsname.; call symputx('bdate_type',vtype(&var2del)); stop; run; data work.&dsname.; set &test..&dsname.; %if &bdate_type=C %then %do; if length(&var2del)=5 and upcase(&var2del) ne 'NA' then SASDATE = input(&var2del,32.)+'30DEC1899'd ; else SASDATE= input(&var2del,anydtdte.); %end; %else %do; SASDATE=&var2del; %end; format SASDATE yymmdd10.; if not missing(sasdate) then fir_age = log(1+(2020-year(SASDATE))); run; /****macro for BDATE****************/ %mend; data _null_; set sashelp.vtable(where=(libname='TEST')); cmd = catx(',', '%changedate('|| strip(libname), strip(memname), 'BDATE'||');'); put cmd=; call execute(cmd); put _N_= memname= ' was submitted to run' ; run; The FULL log is 28 options mprint; 29 30 libname test "C:\Users\pnguyen\Desktop\merge2" access=readonly; NOTE: Libref TEST was successfully assigned as follows: Engine: V9 Physical Name: C:\Users\pnguyen\Desktop\merge2 31 32 proc contents data=test._ALL_ out=contents; 33 run; NOTE: The data set WORK.CONTENTS has 138 observations and 41 variables. NOTE: PROCEDURE CONTENTS used (Total process time): real time 0.15 seconds cpu time 0.14 seconds 34 /*I just change to one country for printing log purpose*/ 35 36 /*******DROP BDATE**************/ 37 %macro changedate(lib,dsname,var2del); 38 data _null_; 39 if 0 then set test.&dsname.; 40 call symputx('bdate_type',vtype(&var2del)); 41 stop; 42 run; 43 44 data work.&dsname.; 45 set &test..&dsname.; 46 %if &bdate_type=C %then %do; 2 The SAS System 11:34 Monday, February 15, 2021 47 if length(&var2del)=5 and upcase(&var2del) ne 'NA' then 48 SASDATE = input(&var2del,32.)+'30DEC1899'd ; 49 else SASDATE= input(&var2del,anydtdte.); 50 %end; 51 %else %do; 52 SASDATE=&var2del; 53 %end; 54 format SASDATE yymmdd10.; 55 if not missing(sasdate) then fir_age = log(1+(2020-year(SASDATE))); 56 run; 57 58 /****macro for BDATE****************/ 59 60 %mend; 61 62 data _null_; 63 set sashelp.vtable(where=(libname='TEST')); 64 cmd = catx(',', '%changedate('|| strip(libname), strip(memname), 'BDATE'||');'); put cmd=; 65 call execute(cmd); 66 put _N_= memname= ' was submitted to run' ; 67 run; cmd=%changedate(TEST,ARGENTINA_MERGE2,BDATE); MPRINT(CHANGEDATE): data _null_; MPRINT(CHANGEDATE): if 0 then set test.ARGENTINA_MERGE2; MPRINT(CHANGEDATE): call symputx('bdate_type',vtype(BDATE)); MPRINT(CHANGEDATE): stop; MPRINT(CHANGEDATE): run; MPRINT(CHANGEDATE): data work.ARGENTINA_MERGE2; WARNING: Apparent symbolic reference TEST not resolved. MPRINT(CHANGEDATE): set &test..ARGENTINA_MERGE2; MPRINT(CHANGEDATE): SASDATE=BDATE; MPRINT(CHANGEDATE): format SASDATE yymmdd10.; MPRINT(CHANGEDATE): if not missing(sasdate) then fir_age = log(1+(2020-year(SASDATE))); MPRINT(CHANGEDATE): run; _N_=1 memname=ARGENTINA_MERGE2 was submitted to run cmd=%changedate(TEST,ZAMBIA_MERGE2,BDATE); MPRINT(CHANGEDATE): data _null_; MPRINT(CHANGEDATE): if 0 then set test.ZAMBIA_MERGE2; MPRINT(CHANGEDATE): call symputx('bdate_type',vtype(BDATE)); MPRINT(CHANGEDATE): stop; MPRINT(CHANGEDATE): run; MPRINT(CHANGEDATE): data work.ZAMBIA_MERGE2; WARNING: Apparent symbolic reference TEST not resolved. MPRINT(CHANGEDATE): set &test..ZAMBIA_MERGE2; MPRINT(CHANGEDATE): SASDATE=BDATE; MPRINT(CHANGEDATE): format SASDATE yymmdd10.; MPRINT(CHANGEDATE): if not missing(sasdate) then fir_age = log(1+(2020-year(SASDATE))); MPRINT(CHANGEDATE): run; _N_=2 memname=ZAMBIA_MERGE2 was submitted to run NOTE: There were 2 observations read from the data set SASHELP.VTABLE. WHERE libname='TEST'; NOTE: DATA statement used (Total process time): real time 0.00 seconds cpu time 0.00 seconds NOTE: CALL EXECUTE generated line. 3 The SAS System 11:34 Monday, February 15, 2021 1 + data _null_; if 0 then set test.ARGENTINA_MERGE2; call symputx('bdate_type',vtype(BDATE)); stop; run; NOTE: DATA statement used (Total process time): real time 0.00 seconds cpu time 0.00 seconds NOTE: Line generated by the CALL EXECUTE routine. 1 + data work.ARGENTINA_MERGE2; set &test..ARGENTINA_MERGE2; SASDATE=BDATE; format SASDATE yymmdd10.; if not _ 22 200 1 !+missing(sasdate) then fir_age = WARNING: Apparent symbolic reference TEST not resolved. ERROR: File WORK.RGENTINA_MERGE2.DATA does not exist. ERROR 22-322: Syntax error, expecting one of the following: a name, a quoted string, ;, CUROBS, END, INDSNAME, KEY, KEYRESET, KEYS, NOBS, OPEN, POINT, _DATA_, _LAST_, _NULL_. ERROR 200-322: The symbol is not recognized and will be ignored. 2 + log(1+(2020-year(SASDATE))); run; NOTE: The SAS System stopped processing this step because of errors. WARNING: The data set WORK.ARGENTINA_MERGE2 may be incomplete. When this step was stopped there were 0 observations and 3 variables. WARNING: Data set WORK.ARGENTINA_MERGE2 was not replaced because this step was stopped. NOTE: DATA statement used (Total process time): real time 0.00 seconds cpu time 0.01 seconds 2 + ; 3 + data _null_; if 0 then set test.ZAMBIA_MERGE2; call symputx('bdate_type',vtype(BDATE)); stop; run; NOTE: DATA statement used (Total process time): real time 0.00 seconds cpu time 0.00 seconds NOTE: Line generated by the CALL EXECUTE routine. 3 + data work.ZAMBIA_MERGE2; set &test..ZAMBIA_MERGE2; SASDATE=BDATE; format SASDATE yymmdd10.; if not _ 22 200 3 !+missing(sasdate) then fir_age = log(1+ WARNING: Apparent symbolic reference TEST not resolved. ERROR: File WORK.AMBIA_MERGE2.DATA does not exist. ERROR 22-322: Syntax error, expecting one of the following: a name, a quoted string, ;, CUROBS, END, INDSNAME, KEY, KEYRESET, KEYS, NOBS, OPEN, POINT, _DATA_, _LAST_, _NULL_. 4 The SAS System 11:34 Monday, February 15, 2021 ERROR 200-322: The symbol is not recognized and will be ignored. 4 +(2020-year(SASDATE))); run; NOTE: The SAS System stopped processing this step because of errors. WARNING: The data set WORK.ZAMBIA_MERGE2 may be incomplete. When this step was stopped there were 0 observations and 3 variables. WARNING: Data set WORK.ZAMBIA_MERGE2 was not replaced because this step was stopped. NOTE: DATA statement used (Total process time): real time 0.00 seconds cpu time 0.00 seconds 4 + ; 68 69 %LET _CLIENTTASKLABEL=; 70 %LET _CLIENTPROCESSFLOWNAME=; 71 %LET _CLIENTPROJECTPATH=; 72 %LET _CLIENTPROJECTPATHHOST=; 73 %LET _CLIENTPROJECTNAME=; 74 %LET _SASPROGRAMFILE=; 75 %LET _SASPROGRAMFILEHOST=; 76 77 ;*';*";*/;quit;run; 78 ODS _ALL_ CLOSE; 79 80 81 QUIT; RUN; Could you please give me a hint to deal with such a problem?   Many thanks and warm regards.     ... View more

Hi @SASKiwi  and @qoit    After adapting your suggestion, my code is as below options mprint; libname test "C:\Users\pnguyen\Desktop\merge2" access=readonly; proc contents data=test._ALL_ out=contents; run; %macro changedate(lib,dsname,var2del); data _null_; if 0 then set test.&dsname.;/*changed*/ call symputx('bdate_type',vtype(bdate)); stop; run; data work.&dsname._;/*changed*/ set test.&dsname.;/*changed*/ %if "&bdate_type"="C" %then %do;/*changed*/ if length(bdate)=5 and upcase(bdate) ne 'NA' then SASDATE = input(BDATE,32.)+'30DEC1899'd ; else SASDATE= input(BDATE,anydtdte.); %end; %else %do; SASDATE=BDATE; %end; format SASDATE yymmdd10.; if not missing(sasdate) then fir_age = log(1+(2020-year(SASDATE))); run; %mend; data _null_; set sashelp.vtable(where=(libname='TEST')); cmd = catx(',', '%changedate('|| strip(libname), strip(memname), 'BDATE'||');'); put cmd=; call execute(cmd); put _N_= memname= ' was submitted to run' ; run; And the log is: MPRINT(CHANGEDATE): data _null_; MPRINT(CHANGEDATE): if 0 then set test.ARGENTINA_MERGE2; MPRINT(CHANGEDATE): call symputx('bdate_type',vtype(bdate)); MPRINT(CHANGEDATE): stop; MPRINT(CHANGEDATE): run; MPRINT(CHANGEDATE): data work.ARGENTINA_MERGE2_; MPRINT(CHANGEDATE): set test.ARGENTINA_MERGE2; MPRINT(CHANGEDATE): SASDATE=BDATE; MPRINT(CHANGEDATE): format SASDATE yymmdd10.; MPRINT(CHANGEDATE): if not missing(sasdate) then fir_age = log(1+(2020-year(SASDATE))); MPRINT(CHANGEDATE): run; _N_=1 memname=ARGENTINA_MERGE2 was submitted to run cmd=%changedate(TEST,ZAMBIA_MERGE2,BDATE); MPRINT(CHANGEDATE): data _null_; MPRINT(CHANGEDATE): if 0 then set test.ZAMBIA_MERGE2; MPRINT(CHANGEDATE): call symputx('bdate_type',vtype(bdate)); MPRINT(CHANGEDATE): stop; MPRINT(CHANGEDATE): run; MPRINT(CHANGEDATE): data work.ZAMBIA_MERGE2_; MPRINT(CHANGEDATE): set test.ZAMBIA_MERGE2; MPRINT(CHANGEDATE): SASDATE=BDATE; MPRINT(CHANGEDATE): format SASDATE yymmdd10.; MPRINT(CHANGEDATE): if not missing(sasdate) then fir_age = log(1+(2020-year(SASDATE))); MPRINT(CHANGEDATE): run; _N_=2 memname=ZAMBIA_MERGE2 was submitted to run NOTE: There were 2 observations read from the data set SASHELP.VTABLE. WHERE libname='TEST'; NOTE: DATA statement used (Total process time): real time 0.00 seconds cpu time 0.00 seconds NOTE: CALL EXECUTE generated line. 1 + data _null_; if 0 then set test.ARGENTINA_MERGE2; call symputx('bdate_type',vtype(bdate)); stop; run; 3 The SAS System 11:34 Monday, February 15, 2021 NOTE: DATA statement used (Total process time): real time 0.00 seconds cpu time 0.00 seconds NOTE: Line generated by the CALL EXECUTE routine. 1 + data work.ARGENTINA_MERGE2_; set test.ARGENTINA_MERGE2; SASDATE=BDATE; format SASDATE yymmdd10.; if not _________ 484 1 !+missing(sasdate) then fir_age = NOTE 484-185: Format $YYMMDD was not found or could not be loaded. Could you please help me to sort it out?   Many thanks. ... View more

And can I ask, whether you have one redundant else here?   %else %do; else SASDATE=BDATE; %end; Warm regards. ... View more

Hi @Tom  Amazing support and comprehensive answer I tested the code successfully outside the macro. I just want to conclude a little bit: I did not get the data from R programmer, it is from Excel (I just check the original files). I agree with all your recent two posts, now the code in total is   libname test "C:\Users\pnguyen\Desktop\merge2"; data _null_; if 0 then set test.argentina_merge2; call symputx('bdate_type',vtype(bdate)); stop; run; data haa_; set test.argentina_merge2; %if &bdate_type=C %then %do; if length(bdate)=5 and upcase(bdate) ne 'NA' then SASDATE = input(BDATE,32.)+'30DEC1899'd ; else SASDATE= input(BDATE,anydtdte.); %end; %else %do; else SASDATE=BDATE; %end; format SASDATE yymmdd10.; if not missing(sasdate) then fir_age = log(1+(2020-year(SASDATE))); run;   Can I ask a further question?   That when I put it into a macro, it turns out an errors regarding the code "if not missing"  The code is data _null_; if 0 then set &outfm.merge2; call symputx('bdate_type',vtype(bdate)); stop; run; data &outfm.merge2_(drop=NAME); set &outfm.merge2; %if &bdate_type=C %then %do; if (length(bdate)=5) and (upcase(bdate) ne 'NA') then SASDATE = input(BDATE,32.)+'30DEC1899'd ; else SASDATE= input(BDATE,anydtdte.); %end; %else %do; else SASDATE=BDATE; %end; format SASDATE yymmdd10.; if not missing(sasdate) then fir_age = log(1+(2020-year(SASDATE))); run; The error log is 134 + data ARGENTINA_merge2_(drop=NAME); set ARGENTINA_merge2; else SASDATE=BDATE; format SASDATE yymmdd10.; if not ____ _________ 160 484 134 !+missing(sasdate) ERROR 160-185: No matching IF-THEN clause. NOTE 484-185: Format $YYMMDD was not found or could not be loaded. 135 + then fir_age = log(1+(2020-year(SASDATE))); run; NOTE: Character values have been converted to numeric values at the places given by: (Line):(Column). 135:34 88 The SAS System 23:21 Saturday, February 13, 2021 NOTE: The SAS System stopped processing this step because of errors. WARNING: The data set WORK.ARGENTINA_MERGE2_ may be incomplete. When this step was stopped there were 0 observations and 70 variables. WARNING: Data set WORK.ARGENTINA_MERGE2_ was not replaced because this step was stopped. My whole code is options compress=yes reuse=yes; options mergenoby=error; OPTIONS MPRINT; %macro ImportAndTranspose( File= , cur= , outf= , StartSheet= , EndSheet= ); %local i; %do i = &StartSheet. %to &EndSheet.; %if &i=1 %then %do; proc import datafile= "&File." out= &outf.&i. dbms= xlsx replace; range= "Sheet1$A:X"; getnames= yes; run; proc sort data= &outf.&i.; by Type; run; %end; %else %if &i=34 %then %do; proc import datafile= "&File." out= &outf.&i. dbms= xlsx replace; range= "Sheet&i.$A:AG"; getnames= yes; run; proc sort data= &outf.&i.; by Type; run; proc transpose data= &outf.&i. out= &outf.&i._out(rename=(COL1=s&i. _NAME_=Year) drop=_label_); by Type; VAR '1988'N - '2019'N; run; data &outf.&i._outx; set &outf.&i._out; if s&i. in: ('NA', '$$', '') then s&i. = " ";/********/ run; %end; %else %if (&i ne 1) and (&i ne 34) %then %do; proc import datafile= "&File." out= &outf.&i. dbms= xlsx replace; range= "Sheet&i.$A:AG"; getnames= yes; run; proc sort data= &outf.&i.; by Type; run; proc transpose data= &outf.&i. out= &outf.&i._out(rename=(COL1=s&i. _NAME_=Year) drop=_label_); by Type; VAR '1988'N - '2019'N; run; data &outf.&i._outx; set &outf.&i._out; if input(s&i., ?? 32.) = . then s&i.2=.;/*added line 21_Jan*/ else s&i.2=input(s&i., 32.);/*added line 21_Jan*/ /*if s&i. in: ('NA', '$$', '..') then s&i.2=.; else if s&i. not in: ('NA', '$$', '..') then s&i.2=input(s&i., 32.);*/ drop s&i.; rename s&i.2=s&i.; run; %end; %end; /* Merging*/ %let outfm = %substr(&outf,1,%length(&outf)-5); data &outfm.merge1; merge %do i =&StartSheet.+1 %to &EndSheet.; &outf.&i._outx(keep= type year s&i.) %end; ; by type year; run; data &outfm.merge2; merge &outf.&StartSheet. &outfm.merge1 ; by type; run; data _null_; if 0 then set &outfm.merge2; call symputx('bdate_type',vtype(bdate)); stop; run; data &outfm.merge2_(drop=NAME); set &outfm.merge2; %if &bdate_type=C %then %do; if (length(bdate)=5) and (upcase(bdate) ne 'NA') then SASDATE = input(BDATE,32.)+'30DEC1899'd ; else SASDATE= input(BDATE,anydtdte.); %end; %else %do; else SASDATE=BDATE; %end; format SASDATE yymmdd10.; if not missing(sasdate) then fir_age = log(1+(2020-year(SASDATE))); run; *********************************************; /* Delete file results to reduce operating*/ *************; %local i; %do i = &StartSheet.+1 %to &EndSheet.; *+1 because sheet 1 behave differently*; proc delete data=&outf.&i.; proc delete data=&outf.&i._out; proc delete data=&outf.&i._outx; %end; *****************; proc delete data=&outf.1; proc delete data=&outfm.merge1; **********************************************; ************************************************; **EXPORT DATASET**; /*https://communities.sas.com/t5/SAS-Programming/making-a-sas7bdat-file/td-p/417487*/ libname myfolder 'C:\Users\pnguyen\OneDrive - Massey University\PhD JOURNEY\2Cfiltering'; proc copy in=work out=myfolder; select &outfm.merge2_; run; quit; /*proc delete data=&outfm.filter;*/ %mend; /*Replicate all files in one folder*/ filename mydir 'C:\Users\pnguyen\Desktop\New folder'; data _null_; did = dopen('mydir'); do i = 1 to dnum(did); fname = scan(dread(did,i),1,'.'); length short_fn $29 currency $3 ; short_fn= cats(substr(fname, 1,length(fname)-3),'_'); currency=substr(fname,length(fname)-2); cmd=cats('%ImportAndTranspose(File=C:\Users\pnguyen\Desktop\New folder\', strip(fname), ',cur=',currency, ',outf=',short_fn,'sheet,startsheet=1,endsheet=45);'); call execute(cmd); end; keep fname; run; Warmest regards. ... View more

Hi @Tom  Many thanks for a very dedicated explanation. I have two questions here. 1. So, you mean, in my case, the result is not wrong if I still apply my current code, the log just generate note and warning "Limit set by ERRORS= option reached."  libname test "C:\Users\pnguyen\Desktop\merge2"; data haa_; set test.argentina_merge2; if (vtype(BDATE)="C") then do; SASDATE = input(BDATE,32.)+'30DEC1899'd ; format SASDATE yymmdd10.; end; else SASDATE=BDATE; fir_age = log(1+(2020-year(SASDATE))); run; 2. Could you please insert your macro into my above code or hint me on how to adapt my code above including your macro?   Many thanks and warmest regards.       ... View more

Hi @Tom  You are totally right! Is this an error? And if it is an error, is there any way to fix it then ?   Warmest regards. ... View more

Hi @Tom    Thank you, It works, just wondering if the code below is okay to you( yes the one I want to test is BDATE)? data haa_; set test.argentina_filterf; if (vtype(BDATE)="C") then do; SASDATE = input(BDATE,32.)+'30DEC1899'd ; format SASDATE yymmdd10.; end; else SASDATE=BDATE; fir_age = log(1+(2020-year(SASDATE))); run; Warm regards.   ... View more

Hi @Shmuel ! The code: options mprint; Libname _9b'C:\Users\pnguyen\Desktop\New folder2' access=readonly; /*I just change to one country for printing log purpose*/ /*******DROP BDATE**************/ %macro delvar(lib,dsname,var2del); data &lib..&dsname; set &lib..&dsname(drop=&var2del); run; %mend; data _null_; set sashelp.vtable(where=(libname='_9B')); cmd = catx(',', '%delvar('|| strip(libname), strip(memname), 'BDATE'||');'); put cmd=; call execute(cmd); put _N_= memname= ' was submitted to run' ; run; Sorry I did not post the whole log. The whole log is as below:   MPRINT(DELVAR): data _9B.ARGENTINA_MERGE2; MPRINT(DELVAR): set _9B.ARGENTINA_MERGE2(drop=BDATE); MPRINT(DELVAR): run; _N_=1 memname=ARGENTINA_MERGE2 was submitted to run NOTE: There were 1 observations read from the data set SASHELP.VTABLE. WHERE libname='_9B'; NOTE: DATA statement used (Total process time): real time 0.00 seconds cpu time 0.01 seconds NOTE: CALL EXECUTE generated line. 1 + data _9B.ARGENTINA_MERGE2; set _9B.ARGENTINA_MERGE2(drop=BDATE); run; NOTE: Data file _9B.ARGENTINA_MERGE2.DATA is in a format that is native to another host, or the file encoding does not match the session encoding. Cross Environment Data Access will be used, which might require additional CPU resources and might reduce performance. ERROR: Write access to member _9B.ARGENTINA_MERGE2.DATA is denied. NOTE: The SAS System stopped processing this step because of errors. And there is no output or result being created.   Many thanks and warm regards. ... View more