topic Re: How to substrate two variables by keywords in variable name? in SAS Programming

How to substrate two variables by keywords in variable name?

lydiawawa — Fri, 15 Feb 2019 19:49:24 GMT

I have a dataset such as the following:

  login_loading_time    check_closing_time    login_closing_time    check_loading_time
    400                      415                   456                    421
    405                      214                   413                    411
    410                      412                   400                    403  
    450                      423                   451                    401
    454                      411                   441                    421
    458                      401                   433                    411
    750                      410                   412                    410
    760                      710                   451                    421
    770                      798                   413                    433

I'm interested in finding out the difference between xxx_loading_time and xxx_closing_time (xxx_loading_time - xxx_closing_time). Here xxx can be 'check' or 'login', and the outcome dataset should look like the following:

check_diff login_diff
  6             -56
 197            -8
  .              .
  .              .

Is there a way to index terms in SAS arithmetic operation. What will be the best way to achieve the outcome?

Any help is much appriciated.

Re: How to substrate two variables by keywords in variable name?

s_lassen — Mon, 18 Feb 2019 09:21:34 GMT

One possibility is to use a macro:

%macro diff(prefixes);
  %local i w;
  %do i=1 %to %sysfunc(countw(&prefixes));
    %let w=%scan(&prefixes,&i);
    &w._diff=&w._loading_time-&w._closing_time;
    drop &w._loading_time &w._closing_time;
    %end;
%mend;

data want;
  set have;
  %diff(login check);
run;

Re: How to substrate two variables by keywords in variable name?

gamotte — Mon, 18 Feb 2019 09:56:25 GMT

Hello,

Here is a solution where the code is generated by call execute's. It uses a loop to retrieve the names

of the dataset's columns with call vnext. Whenever the name contains "loading_time" it generates a line of code that

computes the corresponding difference. Note the "stop" at the end of the data step that prevents it

to read further than the first observation.

data have;
    input login_loading_time check_closing_time login_closing_time check_loading_time;
    cards;
400 415 456 421
405 214 413 411
410 412 400 403 
450 423 451 401
454 411 441 421
458 401 433 411
750 410 412 410
760 710 451 421
770 798 413 433
;
run;

data _NULL_;
    set have;
    length NAME $32.;

    call execute('data want; set have;');

    do while (NAME ne "NAME");
        call vnext(NAME);
        if find(upcase(NAME),"LOADING_TIME") then do;
            basename=scan(NAME,1,'_');
            call execute(cats(basename,'_diff=sum(',basename,'_loading_time,-',basename,'_closing_time);'));
			call execute('keep '||strip(basename)||'_diff;');
        end;
    end;
    call execute('run;');
    stop;
run;

Re: How to substrate two variables by keywords in variable name?

ErikLund_Jensen — Mon, 18 Feb 2019 12:50:51 GMT

Hi @lydiawawa

This seems to be exactly the same question as your other question "Performing operation by keywords in varaible name", also posted friday? - It was thoroughly discussed, and solutions given. I think they should be merged and closed.

Re: How to substrate two variables by keywords in variable name?

lydiawawa — Tue, 19 Feb 2019 14:34:45 GMT

@ErikLund_Jensen I'm not sure how the duplicated post was created. I will try to merge the two. Thanks for notifying!