topic Re: Create a new calculated column in SAS Programming

Create a new calculated column

Ronein — Sun, 19 Jul 2020 04:22:37 GMT

Hello

I have raw data of transactions with following columns:

FROM_ID (ID of the employer that pay wage)

TO_ID (ID of the Employee that receive wage)

From_Bank (Bank where the money transferred from)

I want to create a new column called From_Bank_New that will get the following value:

IF employer has any transaction from bank 100 then in all transactions of this employer From_Bank_New will equal 100.

IF employer doesn't have any transaction from bank 100 then From_Bank_New will equal to the value of column From_Bank.

What is the way to calculate the new column FROM_BANK_NEW?

Data RawTbl;
Input FROM_ID TO_ID FROM_BANK;
cards;
999 1 100
999 2 100
999 3 100
999 4 120
888 5 80
888 6 80
777 7 100
777 8 100
777 9 100
666 10 90
666 11 50
666 12 100
;
run;



Data wanted;
Input FROM_ID TO_ID FROM_BANK FROM_BANK_New;
cards;
999 1 100 100
999 2 100 100
999 3 100 100
999 4 120 100
888 5 80 80
888 6 70 70
777 7 100 100
777 8 100 100
777 9 100 100
666 10 90 100
666 11 50 100
666 12 100 100
;
run;

Re: Create a new calculated column

Kurt_Bremser — Sun, 19 Jul 2020 07:01:31 GMT

Try this:

proc sort dat=have;
by from_id;
run;

data want;
merge
  have
  have (
    keep=from_id from_bank
    rename=(from_bank=from_bank_new)
    where=(from_bank_new = "100")
    in=in100
  )
;
by from_id;
if not in100 then from_bank_new = from_bank;
run;

untested, posted from my tablet. In case of problems, please post complete log of the step.

Re: Create a new calculated column

RichardDeVen — Sun, 19 Jul 2020 14:22:41 GMT

Why do you want

888 5 80 80
888 6 70 70           <--- where did 70 come from

when you have

888 5 80 80
888 6 80 80

Re: Create a new calculated column

RichardDeVen — Sun, 19 Jul 2020 14:27:18 GMT

You can use a DOW loop to compute a flag variable for the group and apply the flag in logic during output in a second DO loop.

Example:

Presume BY rows are contiguous albeit disordered.

data want;
  do _n_ = 1 by 1 until (last.from_id);
    set have;
    by from_id notsorted;  * presume contiguous;

    * compute flag;
    if not flag_bank_100 then
      flag_bank_100 = from_bank eq 100;
  end;

  do _n_ = 1 to _n_;
    set have;

    * apply flag in logic;
    if flag_bank_100 then 
      from_bank_new = 100;
    else
      from_bank_new = from_bank;

    OUTPUT;
  end;

  drop flag_bank_100;
run;

Re: Create a new calculated column

Angel_Larrion — Mon, 20 Jul 2020 15:07:28 GMT

You can use the next code to create the table you want.

proc sql;
create table want as 
select FROM_ID, TO_ID, FROM_BANK,(case when sum(flag)>0 then 100 
								 else FROM_BANK end) as  FROM_BANK_NEW

from	(select FROM_ID, TO_ID, FROM_BANK, sum(from_bank=100) as flag
		from rawtbl
		group by from_id)

group by from_id;
quit;

The subquery creates a flag to identify the transactions that involve bank 100,

if the sum of this flag is greater than 0 it is due to the bank 100 was involved at least once.