Do loop with indexw

[David_Billa]

I've the code as below. I would like to know if there is any issue in indexw function under else if. I have two files in inpath and I want value of TARGET for both the records with flag=1 in output but I'm not getting any value for excel file and flag value is also 0.

Macro variable NL_RB resolves to 1234,4567,7865,0098

Macro variable LH_RB resolves to 0012,0013,1365,0248,7654

Files which I kept in inpath are,

IFR_GT_MS_0012.xlsx

IFR_GT_TR_1_0012_1_20201009T075212.csv

%let function_compont=LH;
%let inpath=/var/sasdata/input/GT;
%let lh_path=/var/sasdata/input/LH;
%let nl_path=/var/sasdata/input/NL;

data files (drop= i id);
    length id 8 msg filename source target $256;
    did=dopen("source");

    if did<=0 then
        do;
            msg=sysmsg();
            put msg;
            stop;
        end;

    do id=1 to dnum(did);
        filename=dread(did,id);

        if (scan(lowcase(filename),-1,'.')='csv' or scan(lowcase(filename),-1,'.')='xlsx') and ("&function_compont" EQ 'NL' or "&function_compont" EQ 'LH') then
            do;
                source="&inpath" ||"/"|| filename;

                /*LH path for LH Rb*/
                if "&function_compont" EQ 'LH' and (scan(lowcase(filename),5,'_') not in ('5601','6010','6020')) then
                    do;
                        flag = 0;

                        do i = 1 to countw("&lh_rb.",",");
                            if indexw(filename,scan("&lh_rb.",i,","),"_") then
                                do;
                                    flag = 1;
                                    target = "&lh_path"||"/"||filename;
                                end;
                        end;
                    end;

                /*LH path for excel file*/
                else if "&function_compont" EQ 'LH' and scan(lowcase(filename),-1,'.')='xlsx' then
                    do;
                        flag = 0;

                        do i = 1 to countw("&lh_rb.",",");
                            if indexw(filename,scan("&lh_rb.",i,","),"_.") then
                                do;
                                    flag = 1;
                                    target = "&lh_path"||"/"||filename;
                                end;
                        end;
                    end;
                else target="&inpath" ||"/"|| filename;
            end;

        output;
    end;

    did=dclose(did);
    drop did msg;
run;

Any help?

filename	source	target	flag
IFR_GT_MS_0012.xlsx	/var/sasdata/INPUT/GT/IFR_GT_MS_0012.xlsx	/var/sasdata/INPUT/LH/IFR_GT_MS_0012.xlsx	1
IFR_GT_TR_1_0012_1_20201009T075212.csv	/var/sasdata/INPUT/GT/IFR_GT_TR_1_0012_1_20201009T075212.csv

[andreas_lds]

Using temporary variable to hold the results of function-calls like scan(lowcase(filename), 5, '_') and using putlog to write the values of those variables to the log is the recommended way to debug code.

[Kurt_Bremser · Accepted solution]

Your code does in fact work for the csv file, but fails for the xlsx, because you missed to add the dot as delimiter in the first INDEXW function.