Do loop with indexw

David_Billa — Thu, 11 Feb 2021 10:06:47 GMT

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

Re: Do loop with indexw

andreas_lds — Thu, 11 Feb 2021 11:19:22 GMT

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.

Re: Do loop with indexw

Kurt_Bremser — Thu, 11 Feb 2021 11:49:04 GMT

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.

topic Re: Do loop with indexw in SAS Programming

Do loop with indexw

Re: Do loop with indexw

Re: Do loop with indexw