CODEWITHSUNDEEP
sassas-macrodo-loops
Keliimek
Keliimek

Reputation: 161

how to use column value in SAS macro do loop

I have a problem with SAS macro. I used do% %then loop command in proc means, but function %index uses the column name as a simple string, not as a column name. But need to check the values in the column.

Here's the macro I wrote.

%let varlist = mzda mzdy vyplat odmena wage payroll SALARY INSTRUCT PAYMENT;

%macro quickndirty;
    %let it_numb = %sysfunc(countw(&varlist.));

    proc means data=CZ_DATA.TRNS_FNCP_D (keep=DB_CLIENT_ID DB_ACCOUNT PAYMENT_INSTRUCT DT_REP DB_BANK_BIC DB_CLIENT_ID)noprint nway;
        class DB_CLIENT_ID DB_ACCOUNT PAYMENT_INSTRUCT;
        output out= SALARY_P2 (keep = DB_CLIENT_ID DB_ACCOUNT PAYMENT_INSTRUCT _FREQ_ );
        where DT_REP = &report_date. 


    %do i=1 %to &it_numb.;      
        %if %index(PAYMENT_INSTRUCT, %scan(&varlist., &i.))>0 %then
            %do;
                and 1=1
            %end;
        %else %if &i. = &it_numb. %then
            %do;
                and 1=0
                    %put zase 2;
            %end;
%end;
        ;
        run;

%mend;

%quickndirty

data sample:

DB_CLIENT_ID DB_ACCOUNT PAYMENT_INSTRUCT DT_REP
79325   1387348961  SALARY 01/20    11feb2020
79322   1387355558  SALARY 02/20    11feb2020

will anyone advise me how to modify the code to use the values from the column? The code is not complete because I must fix this problem first.

I used the loop instead of the following command. if someone has better solution than to loop statement, can you give me some advice? But it must be still macro. But it must still be a macro, the same code will run several times and I don't want to copy the same code again and again.

and (PAYMENT_INSTRUCT contains 'mzda' or
     PAYMENT_INSTRUCT contains 'mzdy' or
     PAYMENT_INSTRUCT contains 'vyplat' or
     PAYMENT_INSTRUCT contains 'odmena' or
     PAYMENT_INSTRUCT contains 'wage' or
     PAYMENT_INSTRUCT contains 'payroll' or
     PAYMENT_INSTRUCT contains 'salary')

Thanks

Upvotes: 0

Views: 963

Answers (1)

Tom
Tom

Reputation: 51621

Looks like for an input of 

%let varlist = mzda mzdy vyplat ;

you want to generate this code:

PAYMENT_INSTRUCT contains "mzda" 
or PAYMENT_INSTRUCT contains "mzdy" 
or PAYMENT_INSTRUCT contains "vyplat"

So your macro logic should be something like:

%let sep=;
%do i=1 %to %sysfunc(countw(&varlist));
  &sep PAYMENT_INSTRUCT contains "%scan(&varlist,&i)"
  %let sep=or;
%end;

Upvotes: 2

Related Questions