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
I'll give you a hint, this is a job for PROC TRANSPOSE.
Agree re: Transpose -- could also accomplish it with a sort and an additional data step with a by statement.
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;
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;
There is no harm in trying it.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.