topic Re: Proc transpose output issues in SAS Programming

Proc transpose output issues

MikkelVejlby — Tue, 06 Jun 2017 10:53:16 GMT

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.

Re: Proc transpose output issues

RW9 — Tue, 06 Jun 2017 11:57:03 GMT

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.

Re: Proc transpose output issues

Astounding — Tue, 06 Jun 2017 14:28:20 GMT

You'll need to know something about your data:

What is the maximum number of observations per customerid
What are the lengths of each of your character variables

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.

Re: Proc transpose output issues

Peter_C — Tue, 06 Jun 2017 19:36:28 GMT

Could be simpler
by retaining the output _file_ buffer until last.customerID
Something like:

Data _null_ ;
file "youroutputfile.csv" dsd lrecl= 32767 ;
Set your.data ;
by customerID ;
if first.customerID then put customerID @@ ;
put AccountID AccountAmount AccountStartDate AccountEndDate AccountInfoX AccountInfoY @@
If last.customerID ;
put ;
run ;

This should generate a .csv file providing what was requested, except perhaps this would have no column headers.

Re: Proc transpose output issues

Peter_C — Tue, 06 Jun 2017 19:38:27 GMT

Needs a semicolon ... missing from one of the trailing @@