Hi SAS Experts,
Today I face a problem listed in the title of this post
I have a code to run through all files in one folder
%macro variablescalculation(File=,outf=);
/*CODE of the macro*/
%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,'.');
/*At this point I assign that the filename has the tail is xlsx or sas7bdat, if not, use another way
fname= ARGENTINA_FILTERF*/
length short_fn $29;
short_fn= substr(fname, 1,length(fname)-8);
/*short_fn=ARGENTINA*/
cmd=cats('%variablescalculation(File=C:\Users\pnguyen\Desktop\New folder\',
strip(fname),',outf=',short_fn,');');
call execute(cmd);
end;
keep fname;
run;
I only put one file in this folder to test if the result is familiar with the result running for one file only out of macro.
I also run the code variablescalculation outside the macro to compare. The result from the macro above and non-macro code are totally similar.
However, there is only two things happened is regarding the code above, SAS EG showed a notice that
And there was no log being shown with a notice
It is really interesting to me because there was only three output generated by the code, how come it says 135 dataset created.
I also attach my variablescalculation macro code as below
Libname input 'C:\Users\pnguyen\Desktop\New folder';
data argentina;
set input.argentina_filterf;
run;
*Set up lag variables;
data argentinalag;
set argentina;
by type;
lags7= ifn(first.type,.,lag(s7));
/*create other lag variables here*/
lags3= ifn(first.type,.,lag(s3));
lags18= ifn(first.type,.,lag(s18));
*log(0) in meaningless so set these obs equalling to missing;
if n(s27,s2)=2 then if (1+s27/s2)>0 then do;
cf_ope_act=log(1+s27/s2);
end;
lagcf_ope_act = ifn(first.type,.,lag(cf_ope_act));
*equal to using call missing
lagcf_ope_act = lag(cf_ope_act)
if first.type then call missing (lagcf_ope_act);
lags29= ifn(first.type,.,lag(s29));
lags22= ifn(first.type,.,lag(s22));
lags43= ifn(first.type,.,lag(s43));
run;
*********************;
*Rename and ;
data argentina_ (rename=(s2=tot_ass s3=net_sal s4=fix_ass_net s5=acc_rec_day
s6=cogs s7=acc_pay s8=inv_day s9=inv_tur s10=sel_gen_adm
s11=ope_lea_exp s12=r_and_d s13=goodwill s14=fix_ass_gross
s15=inv_tot s16=ope_inc s17=tot_lia s18=investment
s19=cas_div_tot s20=mtbv s21=boo_val_per_sha s22=p
s23=roa s24=net_inc s25=gross_inc s26=fcf_per_sha
s27=net_cf_ope_act s28=ni_bf_ext_ite s29=mar_cap
s30=tot_deb s31=roe s32=dep s33=pri_vola s34=acc_sta
s35=gdp s36=rea_int_rat s37=len_int_rat s38=tax
s39=exc_rat s40=imp_over_gdp s41=inflation s42=unemployment
s43=gdp_per_cap s44=mar_cap_over_gdp s45=fdi)
drop= i) ;
set argentinalag;
***Calculation here
calculation still using old var name;
/*Total Asset turnover */
if n(s2,s3)=2 and s2>0 then
tot_ass_tur=s3/s2;
/*Fixed Asset turnover */
if n(s3,s4)=2 and s4 > 0 then
fix_ass_tur=s3/s4;
/* Day of Payable Outstandings*/
if s6>0 and n(s7,lags7,s6)=3 then
dpo=365*0.5*(lags7+s7)/s6;
/*Cash Conversion Cycle aka screen 12*/
if n(s5,s8,dpo)=3 then
ccc= s5+s8-dpo;
If ccc > 365 then delete;
/* I do not use ccc= sum(dpo,s5,s8) because when using sum, it may sum up two arguments if there is
one missing, but if it is the case, it will not be fair, because you can not compare
days generated from 3 components and 2 components*/
/*Receivables turnover*/
if (s5 ne 0) and (s5 ne .)then
rec_tur = 365/s5;
/*Account Payable turnover*/
if (dpo ne 0) and (dpo ne .) then
acc_pay_tur = 365/dpo;
/*Firm age = log(1+age) calculating based on BDATE*/
SASDATE = input(BDATE,32.)+'30DEC1899'd ;
format SASDATE yymmdd10.;
fir_age = log(1+(2020-year(SASDATE)));
/*https://communities.sas.com/t5/SAS-Programming/Converting-date-to-number-forth-and-back/m-p/715774#M221120*/
/*Capital Intensity*/
if n(s14,s15)=2 and (s14 + s15) ne 0 then
cap_int = s14/(s14+s15);
/*Profit*/
if n(s16,s2)=2 and s2 > 0 then
profit = s16/s2;
/*Leverage*/
if n(s17,s2)=2 and s2 > 0 then
leverage = s17/s2;
/*Tangibility*/
if n(s4,s2)=2 and s2 > 0 then
tangibility = s4/s2;
/*Dividend*/
if n(s19,s2)=2 and s2 > 0 then
dividend = s19/s2;
/*firm size (ln(assets)*/
if s2 > 0 then
firm_size = log(s2);
/*GDP ln(GDP)*/
if s35>0 then
lngdp = log(s35);
/*Market to book value aka screen 10*/
if n(s21,s22)=2 and s21 > 0 then mar_to_boo = s22/s21;
if (mar_to_boo < 0.01 and mar_to_boo ne .) or (mar_to_boo > 3) then delete;
/*Sales growth*/
if n(s3,lags3)=2 and lags3 ne 0 then
sal_gro = (s3-lags3)/lags3*100;
/*Gross Margin*/
if n(s3,s6)=2 and s3 ne 0 then
gro_mar = (s3-s6)/s3;
/*Net profit margin = net profit/sale and net profit = net income*/
if n(s3,s24)=2 and s3 ne 0 then
net_pro_mar = s24/s3;
/*Gross profit margin*/
if n(s3,s25)=2 and s3 ne 0 then
gro_pro_mar = s25/s3;
/*Market value of equity*/
if s29>0 then
log_mve = log(s29);
/*Free Cash Flow- equalling one if higher than 0, 0 otherwise*/
if s26>0 then fcf = 1;
else fcf = 0;
/*investment growth rate*/
if s18>0 and lags18>0 then
inv_gro_rat = log(s18)-log(lags18);
/*deltacf*/
if n(cf_ope_act,lagcf_ope_act)=2 then
deltacf = cf_ope_act - lagcf_ope_act;
/*logarithm of per capita GDP in US dollars*/
if s43>0 then
lngdp_per_cap = log(s43);
/* Earnings*/
if n(s28,lags29)=2 and (lags29 > 0) then
earnings = s28/lags29;
/*Return*/
if n(s22,lags22)=2 and (lags22 > 0) then
return= (s22 - lags22)/lags22;
/*Leverage*/
if n(s30,s29)=2 and (s29 ne 0) then
leverage = s30/s29;
/*research and development*/
if n(s3,s12)=2 and (s3 ne 0) then
r_and_d_over_sal = s12/s3;
/*Investment cycle*/
if n(s32,s2)=2 and (s2 ne 0) then
inv_cyc = s32/s2;
/*GDP growth*/
if n(s43,lags43)=2 and lags43 ne 0 then
gdp_per_cap_gro = (s43 - lags43)/lags43;
/*IFRS*/
if s34 = "IFRS" then ifrs = 1;
else ifrs = 0;
run;
proc means data=argentina_;
run;
I tried to find a solution online and adjust the number of threshold to 300
but the above notice still exists
I am wondering if you can help me to sort it out.
Many thanks and warm regards.
... View more
