topic Re: How to transform accounts and balances are on one line in SAS Programming

How to transform accounts and balances are on one line

Lvp8906 — Sat, 18 Apr 2020 22:29:04 GMT

Here's the photo of my data

And here's my code

DATA ACCOUNTS;
infile datalines dsd dlm=',';
INPUT Customer $ Account $ Balance;
DATALINES;
Smith ,Checking ,1000.00
Smith, Saving ,4000.00
Smith ,Mortgage ,150000.00
Smith ,Credit Card ,500.00
Jones ,Checking ,973.78
Jones ,Savings ,2613.44
Jones ,Mortgage , .
Jones ,Credit card ,140.48
Brown ,Checking ,200.23
Brown ,Savings ,402.88
Brown ,Mortgage ,74000.00
Brown ,Credit Card , . 
RUN;
PROC PRINT DATA= ACCOUNTS;
Run;

Beginner here... the question above are confusing, she want us to have accounts balance credit mortgage into one line and the rest of column show the balance. in the input line, should I just type in INPUT: Customer checking savings Mortgage credit card and retype the datalines with balance? How is this work

Re: How to transform accounts and balances are on one line

PaigeMiller — Sat, 18 Apr 2020 22:59:26 GMT

I'll give you a hint, this is a job for PROC TRANSPOSE.

Re: How to transform accounts and balances are on one line

SASKiwi — Sat, 18 Apr 2020 23:05:13 GMT

Apart from PROC TRANSPOSE here is another way:

DATA ACCOUNTS;
infile datalines dsd dlm=',';
INPUT Customer $ Account_Type Account $ Balance;
DATALINES;
Smith,1,Checking,1000.00
Smith,2,Savings,4000.00
Smith,3,Mortgage,150000.00
Smith,4,Credit Card,500.00
Jones,1,Checking,973.78
Jones,2,Savings,2613.44
Jones,3,Mortgage,.
Jones,4,Credit Card,140.48
Brown,1,Checking,200.23
Brown,2,Savings,402.88
Brown,3,Mortgage,74000.00
Brown,4,Credit Card,.
; 
RUN;

proc sort data = ACCOUNTS;
  by customer;
run;
 
data want;
  keep customer Checking Savings Mortgage Credit_Card;
  set accounts;
  by customer;
  array accounts (*) Checking Savings Mortgage Credit_Card;
  retain Checking Savings Mortgage Credit_Card;
  if first.customer then call missing(Checking, Savings, Mortgage, Credit_Card);
  do i = 1 to dim(accounts);
    if i = Account_Type then accounts(i) = balance;
  end;
  if last.customer;
run;

proc print data = want;
  id customer;
run;

Re: How to transform accounts and balances are on one line

djmangen — Sat, 18 Apr 2020 23:06:09 GMT

Agree re: Transpose -- could also accomplish it with a sort and an additional data step with a by statement.

Re: How to transform accounts and balances are on one line

Lvp8906 — Sat, 18 Apr 2020 23:43:16 GMT

If i use PROC transpose how should it be done?

like this?

PROC transpose data= ACCOUNTS
out=output-Accounts ;
var Customer ;

by Balance;
run;
PROC PRINT DATA= output-Accounts;
RUN;

Re: How to transform accounts and balances are on one line

SASKiwi — Sun, 19 Apr 2020 01:29:47 GMT

There is no harm in trying it.