Excel Spreadsheet

Aar684 — Tue, 07 Jul 2009 13:08:59 GMT

Hi All,

Need some help here. Will probably be fairly basic for most of you. I have 50 columns of data that are headed by an account number. Under each of the 50 account numbers I have 720 rows of data. What I need to do is take each of the 50 columns of data and put them into one column, minus the account numbers. The account numbers would then be in the next column over. The account number needs to be listed next to each row of data. Example :

Column A Column B
data account #
data account #
data account #
data account #
data account #

Once the 720 rows of the first account number are in column A, the next 720 from the next account # need to move to column A and start writing that account # into column B. Any ideas?

Thanks very much in advance.

Aaron

Re: Excel Spreadsheet

Cynthia_sas — Tue, 07 Jul 2009 16:11:21 GMT

Hi:
I'm having a hard time envisioning the "before" and "after". This is what I imagine. You have data that looks like this (sort of) but with 50 accts and 720 rows. I made some fake data with only 5 accounts and 5 rows. You said your columns were "headed" by an account number. I took that to mean that the column NAMES were acct1, acct2, acct3, etc.:
[pre]
acct1 acct2 acct3 acct4 acct5
101 1101 11101 111101 1111101
102 1102 11102 111102 1111102
103 1103 11103 111103 1111103
104 1104 11104 111104 1111104
105 1105 11105 111105 1111105
[/pre]

And you want this output form:
[pre]
column_A column_B
101 acct1
102 acct1
103 acct1
104 acct1
105 acct1
1101 acct2
1102 acct2
1103 acct2
1104 acct2
1105 acct2
11101 acct3
11102 acct3
11103 acct3
11104 acct3
11105 acct3
111101 acct4
111102 acct4
111103 acct4
111104 acct4
111105 acct4
1111101 acct5
1111102 acct5
1111103 acct5
1111104 acct5
1111105 acct5
[/pre]

And you want the output to be in Excel. This last part is the easiest. You can either create a final dataset in the structure you want and use proc export or the Excel Libname engine to get the data into Excel. Or, you can use ODS CSV, ODS HTML or ODS TAGSETS.EXCELXP to get a report (probably PROC PRINT) output into a type of file that Excel can open.

As for how to transform your data, if I have correctly described the transformation, you have several choices:
1) investigate PROC TRANSPOSE
2) investigate the use of arrays in a data step program to transform your data from "wide" data structure to "narrow" data structure
3) use PROC SQL (probably with multiple passes) to create the final dataset

There have been quite a few previous forum postings on all 3 of these methods for transforming data.

cynthia

topic Excel Spreadsheet in SAS Programming

Excel Spreadsheet

Re: Excel Spreadsheet