topic how to convert multiple columns to single column in SAS Enterprise Guide

how to convert multiple columns to single column

deleted_user — Fri, 03 Aug 2007 09:27:04 GMT

I have a Oracle library in my SMC where i have a table supplier in which i have column sundry_code which occurs 10 since in oracle it is written in
sundry_code * 10
bcoz of that when i open this table in EG i get 10 columns for 1 row.
sundry_code_001,sundry_code_002,...sundry_code_010.
Now i want to convert these 10 columns back to 1 column.
In this also i have one more column sundry_val which occurs for 10 times.
So how should i go ahead doing in EG to get only two columns like sundry_code and sundry_val

Re: how to convert multiple columns to single column

Cynthia_sas — Fri, 03 Aug 2007 17:26:47 GMT

Hi:
Is this what you mean? You have data that looks like this (I only typed 3 instead of 10):
[pre]
What does the data look like

sundry_ sundry_ sundry_ sundry_ sundry_ sundry_
name code_001 code_002 code_003 val_001 val_002 val_003

alan aaa bbb ccc 100 200 300
bob aaa ccc fff 111 333 777

[/pre]

And, your data probably has more columns or variables, but what you WANT falls into 2 possibilities in my mind.
[pre]
Possibility 1: One Row for each sundry_code
name sundry_code sundry_val

alan aaa 100
alan bbb 200
alan ccc 300
bob aaa 111
bob ccc 333
bob fff 777
[/pre]
or you want to "collapse" the information like this
[pre]
Possibility 2: Collapse all the codes and values together

name sundry_code sundry_val
alan aaa bbb ccc 600
bob aaa ccc fff 1221
[/pre]

The technique you use will depend on what your input data looks like and what your desired output data should look like. For Possibility 1, you would have to treat the numbered codes and values as an array and then you'd "unarray" the values, creating a separate row for every array member. For Possibility 2, you would have to concatenate all the separate codes together and then sum up all the separate values.

In order to achieve Possibility 1, you'd have to use a DATA step program and the ARRAY statement and if you are not familiar with these techniques, then Tech Support might be your best bet for help with this task. In order to achieve Possibility 2, you could use either a PROC SQL query, such as the kind you build with EG or you could use a DATA step program. Again, Tech Support can help you figure out the best way to accomplish what you need to do.

cynthia

Re: how to convert multiple columns to single column

deleted_user — Mon, 06 Aug 2007 13:47:33 GMT

thanx ya my case possibilty1.Is there any task in EG which i can use directly to get that output

Re: how to convert multiple columns to single column

Cynthia_sas — Mon, 06 Aug 2007 15:42:38 GMT

Hi,
I don't know of any task that would do it directly. I suppose you could build multiple queries using the query task:
[pre]
proc sql;
create table hold1 as
select name,
sundry_code_001 as sundry_code,
sundry_val_001 as sundry_val
from lib.file;
quit;
[/pre]

And then you'd end up with 10 "work" files that you'd have to concatenate back together.

Or, you could open up a code node in EG and use a DATA Step program to build your output file with one pass through the data. For example, to actually make the output that I showed as Possibility 1, I made a work file called work.testit with just 3 numbered sundry codes and then this program "unarrayed" the variables and created one row for each name:
[pre]
data Poss1(keep=name sundry_code sundry_val);
set work.testit;
array sc $ sundry_code_001 - sundry_code_003;
array sv sundry_val_001 - sundry_val_003;
do i = 1 to 3 by 1;
sundry_code = sc(i);
sundry_val = sv(i);
output;
end;
run;
[/pre]

The result of this program is a dataset, WORK.POSS1 that contains the data as shown for Possibility 1 choice above. The DATA step has the ability to use the ARRAY statement to treat your "numbered" variables as though they are array members. So there's an ARRAY statement for sundry_code_001 - sundry_code_003 (a character $ array) and another ARRAY statement for sundry_val_001 - sundry_val_003 (a numeric array). Then, the DO loop creates the new variables sundry_code and sundry_val from the array members (using the sc(i) and sv(i) reference method). Finally, the OUTPUT statement outputs one row for every iteration through the loop. The KEEP option guarantees that the final dataset contains only the columns I want to keep in the final dataset.

For a production program, I would recommend a few more changes to guarantee that you did not output any rows if both sundry_code or sundry_val were missing. And perhaps add any format or label statements that would make the data more meaningful when it was used.

For more help with figuring out how to do this in EG, your best bet is to contact Tech Support.

cynthia

Re: how to convert multiple columns to single column

deleted_user — Mon, 06 Aug 2007 17:04:57 GMT

thanx,
I got one task in EG which is stack but at a time it converts that 10 columns into one column.
But if i have one more column with 10 occurences then i have to again use stack.
then to merge them is problem..
so can you suggest me how to go ahead

Re: how to convert multiple columns to single column

Cynthia_sas — Mon, 06 Aug 2007 17:57:27 GMT

Hi:
I forgot about the STACK task. But the reason the STACK task didn't pop into my thought process on this question is that it uses PROC TRANSPOSE and it's a bit harder to get your OTHER variables to carry along. With the STACK Task, you could pick more than one column to stack, but the resulting data set will look like this:
[pre]
name ValueSource StackedValue
alan sundry_val_003 300
alan sundry_val_002 200
alan sundry_val_001 100
alan sundry_code_003 ccc
alan sundry_code_002 bbb
alan sundry_code_001 aaa
bob sundry_val_003 777
bob sundry_val_002 333
bob sundry_val_001 111
bob sundry_code_003 fff
bob sundry_code_002 ccc
bob sundry_code_001 aaa
[/pre]

You also said you wanted the values to be named sundry_code and sundry_val. And in order to get those names for your variables, you would have had to modify the code.

Your best bet at this point, is to contact Tech Support for help with the STACK task and then you still may have to modify the PROC TRANSPOSE code to get the right output dataset you want. But, if you're going to have to modify code, then you still might want to consider the ARRAY solution above in a code node. Tech Support could help you with either solution.

cynthia