topic Re: SAS programming in SAS Programming

SAS programming

Sultana — Wed, 04 Aug 2021 21:37:18 GMT

Hello,

I have a data in CSV format where in the account ID and type are the sources, so I want the output as from account Id take the source as AB Post-Tax and CD PRE-TAX having the amount total but then from the type I need to take the Roth Deferral and SafeMatch also as the source with the amount so where I have

567-67-4444

AB Post-Tax

Roth Deferral

1,234.77

The AB Post-Tax is not to be taken instead to take the Roth Deferral and the amount reflected for the same. Please advise. the same is true for SafeMatch. This is just sample data it has 1000 rows as such so I cannot hard code.

SSN	AccountID	Type	Amount
123-11-2222	AB Post-Tax		1,234.00
345-45-3333	AB Post-Tax		1,235.00
567-67-4444	AB Post-Tax	Roth Deferral	1,234.77
789-01-5555	CD PRE-TAX		1,254.77
321-11-1111	CD PRE-TAX		1,734.77
543-22-6666	CD PRE-TAX	SafeMatch	1,834.77

Re: SAS programming

Reeza — Wed, 04 Aug 2021 23:04:25 GMT

Unfortunately the question is unclear to me.

Please clearly specify what is the input, what is the expected output and the logic between two steps.

@Sultana wrote:

Hello,

I have a data in CSV format where in the account ID and type are the sources, so I want the output as from account Id take the source as AB Post-Tax and CD PRE-TAX having the amount total but then from the type I need to take the Roth Deferral and SafeMatch also as the source with the amount so where I have

567-67-4444 AB Post-Tax Roth Deferral 1,234.77

The AB Post-Tax is not to be taken instead to take the Roth Deferral and the amount reflected for the same. Please advise. the same is true for SafeMatch. This is just sample data it has 1000 rows as such so I cannot hard code.

SSN AccountID Type Amount

123-11-2222 AB Post-Tax 1,234.00

345-45-3333 AB Post-Tax 1,235.00

567-67-4444 AB Post-Tax Roth Deferral 1,234.77

789-01-5555 CD PRE-TAX 1,254.77

321-11-1111 CD PRE-TAX 1,734.77

543-22-6666 CD PRE-TAX SafeMatch 1,834.77

Re: SAS programming

ChrisNZ — Thu, 05 Aug 2021 01:58:05 GMT

One way to do this is to use the UPDATE BY statement.

Another is to use a SET KEY= statement or a hash table.

In all cases, you need to ensure there are no duplicates, i.e. AB Post-Tax only has type Roth Deferral

Using a hash table:

data WANT;
  set HAVE;
  if _N_=1 then do;
   dcl hash H(dataset:'HAVE(where=(TYPE ne " ")) ');
   H.definekey('ACCOUNTID' );
   H.definedata('TYPE');
   H.definedone();
  end;
  if TYPE=' ' then H.find();
run;

Re: SAS programming

Sultana — Thu, 05 Aug 2021 14:04:17 GMT

This is my input data

SSN AccountID Type Amount
123-11-2222 AB Post-Tax 1,234.00
345-45-3333 AB Post-Tax 1,235.00
567-67-4444 AB Post-Tax Roth Deferral 1,234.77
789-01-5555 CD PRE-TAX 1,254.77
321-11-1111 CD PRE-TAX 1,734.77
543-22-6666 CD PRE-TAX SafeMatch 1,834.77

and I want the output data as

Src
Amount
AB Post-Tax 2,469
CD PRE-TAX 1,234.77
Roth Deferral 2,989.54
SafeMatch 1,834.77
[image: image.gif]