turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Find a Community

- Home
- /
- SAS Programming
- /
- Base SAS Programming
- /
- Excel Spreadsheet

Topic Options

- RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

07-07-2009 09:08 AM

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

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

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Aar684

07-07-2009 12:11 PM

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

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