topic Re: Create new columns based on condition in New SAS User

Create new columns based on condition

clipsia — Sun, 01 Nov 2020 20:00:31 GMT

Hi, I am having problems creating a new Column where I have to use a condition.

The context is: I have a table with the columns:

-ID

-credit card: it can be visa or master.

-amount: the amount in $

The objetive is not to be repetitive with the id if the client has visa and master. So I need 2 new columns where: if the credit card is from visa the new column "Amount_Visa" has the consolidated amount. And if its from master, the new column "Amount_Master" has its amount.

Now I have this:

ID	Credit card	Amount
1	visa	230.60
1	master	456.30

And I need this:

ID	Credit Card	Amount_Visa	Amount_Master
1	Visa/Master	230.60	456.30

Thank you!

Re: Create new columns based on condition

novinosrin — Sun, 01 Nov 2020 20:08:49 GMT



data have;
input ID	Credit_card $	Amount;
cards;
1	visa	230.60
1	master	456.30
;

proc transpose data=have out=want(drop=_name_) prefix=Amount_;
 by id;
 var amount;
 id credit_card;
run;
proc print noobs;run;

Re: Create new columns based on condition

PeterClemmensen — Sun, 01 Nov 2020 20:11:28 GMT

data have;
input ID CreditCard $ Amount;
datalines;
1 visa   230.60
1 master 456.30
2 visa   230.60
2 master 456.30
;

data want;
   do until (last.ID);
      set have;
      by ID;
      CreditCard = "V/M";
      Amount_Visa	= Amount;
      Amount_Master = Amount;
   end;
   drop Amount;
run;

Re: Create new columns based on condition

clipsia — Sun, 01 Nov 2020 20:12:47 GMT

Thank you both! But does it help if I have 3000 rows in this table??

Re: Create new columns based on condition

clipsia — Sun, 01 Nov 2020 20:14:01 GMT

I need a data set like this:
data want;
set table;
Amount_VISA=Amount;
if credit card='Visa' then Amount_VISA=Amount;
else Amount_MASTER=Amount;
drop amount;
run;

Re: Create new columns based on condition

clipsia — Sun, 01 Nov 2020 20:14:39 GMT

the important thing is to get off the duplicated ID rows

Re: Create new columns based on condition

PeterClemmensen — Sun, 01 Nov 2020 20:53:38 GMT

Both approaches handles 3000 obs easily.

Re: Create new columns based on condition

Kurt_Bremser — Sun, 01 Nov 2020 21:05:51 GMT

@clipsia wrote:
Thank you both! But does it help if I have 3000 rows in this table??

With a sufficiently slow computer, it might actually need more than 0.01 seconds.