Hello.
I have a problem with the format I am required to deliver.
The data is not problematic:
CustomerId, AccountID, AccountAmount, AccountStartDate, AccountEndDate, AccountInfoX, AccountInfoY, ...
filled with data like
custA, Acc1, 10, 1/1-2015, 1/1-2025, 'Xa', 'Ya', ...
custA, Acc2, 20, 1/1-2016, 1/1-2026, 'Xb', 'Yb', ...
custA, Acc3, 30, 1/1-2017, 1/1-2027, 'Xc', 'Yc', ...
custB, Acc4, 40, 1/1-2016, 1/1-2021, 'Xd', 'Yd', ...
custB, Acc5, 50, 1/1-2017, 1/1-2022, 'Xe', 'Ye', ...
etc
The format I have to deliver is not that easy (for me at least):
custA, Acc1, 10, 1/1-2015, 1/1-2025, 'Xa', 'Ya', ..., Acc2, 20, 1/1-2016, 1/1-2026, 'Xb', 'Yb', ..., Acc3, 30, 1/1-2017, 1/1-2027, 'Xc', 'Yc', ...
custB, Acc4, 40, 1/1-2016, 1/1-2021, 'Xd', 'Yd', ..., Acc5, 50, 1/1-2017, 1/1-2022, 'Xe', 'Ye', ...
etc
Ie for each customer all accounts "to the right" as opposed to one under the other.
I have looked at proc transpose, but it doesn't seem to be quite what I need.
Anybody has an idea?
Thanks in advance
Mikkel
version information:
Copyright (c) 2002-2008 by SAS Institute Inc., Cary, NC, USA.
SAS (r) Proprietary Software 9.2 (TS2M3)
Licensed to BNP PF EX LASER SERVEUR 8 CORES AIX MIGRATION, Site xxx.
This session is executing on the AIX 5.3 (AIX 64) platform.
Post test data in the form of a datastep, I neither want to type it out nor guess the structure of the data. Examples can be found in most posts.
As for your required output, it looks like a simple CSV file output, so a datastep like:
data _null_; set your data; file "youroutput.csv"; by customerid; if first.customer then put customerid; put catx(",",accountid,accoutnamount,accountstartdate...); run;
What I would advise however is to not transpose the data up. This will make reading in the file so much harder. The way you have the data now, proc export to CSV would be far easier for the recipient to process.
You'll need to know something about your data:
Then you can code it like this:
data want;
array acc {20} $ 10 AccountID1 - AccountID20;
array aca {20} AccountAmount1 - AccountAmount20;
array stdt {20} $ 10 AccountStartDate1 - AccountStartDate20;
array endt {20} $ 10 AccountEndDate1 - AccountEndDate20;
n = 0;
do until (last.customerid);
set have;
by customerid;
n + 1;
acc{n} = AccountID;
aca{n} = AccountAmount;
stdt{n} = AccountStartDate;
endt{n} = AccountEndDate;
end;
drop n;
run;
Obviously, you will need to add more arrays to the program, to account for the additional variables in your data set. But this is a reasonable framework for transforming a SAS data set.
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.