topic Re: How to move a column and append it to another column of same data table? in SAS Procedures

How to move a column and append it to another column of same data table?

xenoirita — Sun, 24 Feb 2013 13:23:10 GMT

I have data table with 6 col & 10,000 rows.

Col (1st, 2nd, 3rd) are random number. Meanwhile col (4th, 5th, 6th) are tabulate based on the random number in col (1st, 2nd, 3rd).

oHow do i cut all 10,000 row data in col (4th, 5th, 6th) and paste into rows 10,001 of col (1st, 2nd 3rd) without copying the formula?

Here is my script but it doesn't work:

dt_temp = New Table ( "Temp", Add rows( 10000 ),

New Column( "Build", character, nominal, formula( "L1" ) ,

New Column( "L1u", random, formula( Random Normal(a,b) ),

New Column( "L1v", random, formula( Random Normal(c,d) ) ,

New Column( "Build2", character, nominal, formula( "L2" ) ),

New Column( "L2u", numeric, continuous, formula( :L1u *123 + :L1v*345) ),

New Column( "L2v", numeric, continuous, formula( :L12 *223 + :L1v*562) ));

Current Data Table() << Subset( Columns( Column( 1 ), Column( 2 ), Column( 3 ), Column( 4 ), Column( 5 ), Column( 6 ) ), Copy formula( 0 ) );

Current Data Table () << Add rows(10000);

?? C1_list = Column(1) << Get Values;

?? C4_list = Column(4) << Get Values;

?? Column(1) << Insert Into( Insert (C1_list, C4_list, 100001)) ;

?? C2_list = Column(1) << Get Values;

?? C5_list = Column(5) << Get Values;

?? Column(2) << Insert Into( Insert (C2_list, C5_list, 100001)) ;

?? C3_list = Column(1) << Get Values;

?? C6_list = Column(5) << Get Values;

?? Column(3) << Insert Into( Insert (C3_list, C6_list, 100001)) ;

Current Data Table () << delete columns (column(4),column(5); column(6);

Re: How to move a column and append it to another column of same data table?

art297 — Sun, 24 Feb 2013 16:40:08 GMT

It sounds like you are trying to get SAS to work like a spreadsheet and I'm not familiar with the various functions you are using.

You can easily get SAS to produce a second record for each record in your dataset, simply by including the relevant code and output statements. However, for any one to show you how, we/they would have to understand what you want to be different between the two records.

Re: How to move a column and append it to another column of same data table?

Cynthia_sas — Mon, 25 Feb 2013 03:50:17 GMT

Hi:

I agree with Art. Your "script" doesn't look like a SAS program and I have a hard time visualizing what you want. Your question about how to cut and paste without copying formulas doesn't make sense for 2 reasons: 1) you don't typically cut and paste SAS data and 2) SAS data cells do not contain formulas like an Excel spreadsheet.

But, for the sake of getting a better picture, let's say that you have 3 rows (easier to visualize than 10000 rows). And that something like what's shown below are the "before" data -- the data you have when you start. I have only shown the column names as col1-col6. I did not bother with row numbers. I also did not bother to make col1, col2 and col3 random numbers because I want to understand structure and the data manipulation that you want:

Before, what you have (3 rows):
-----------------------------
col1 col2 col3 col4 col5 col6
11    12   13   54   55   56
21    22   23   64   65   66
31    32   33   74   75   76

So, the way I interpret what you want, you would move the 54, 55, 56 from ROW1 and move it to ROW4, then you would take the 64, 65 and 66 from ROW2 and move it to ROW5, etc. Like this:

After, what you want (6 rows):
-----------------------------
col1 col2 col3 col4 col5 col6
11    12   13
21    22   23
31    32   33
54    55   56
64    65   66
74    75   76

Is that at least partly correct? If that understanding is correct, then perhaps people can begin to understand what you want. If that understanding is not correct, then you will have to correct the "picture" of what your data looks like when you start and what it is that you want to end up with. If you can clearly explain what you are trying to achieve using just 3 rows of fake data, then it will be clearer to everyone what type of data manipulation you want.

Next, since there is no cutting and pasting of SAS data, and no formulas, that part of your question is distracting. It would be more useful to know a bit about the purpose of this exercise, what form your starting data is in (is your data already in a SAS dataset; is your data in a 3rd party database, like Oracle or DB2; is your data in Microsoft Access or Microsoft Excel). You say that you have a "data table", but not what form of "data table" it is. Also useful to know would be what SAS tools you have available to use. Are you using SAS Enterprise Guide? SAS Display Manager? SAS on Windows? SAS on the mainframe? SAS on Unix? Why do you need to perform this data manipulation, what purpose does it serve and what form of output do you need? A new table? A report? A CSV file? A graph?

Finally, it would be helpful to know your experience level with SAS. Have you used SAS procedures? Which ones? Have you written DATA step programs? Have you used PROC SQL? You can help the forum community help you by providing more information about your data and your question.

cynthia

Re: How to move a column and append it to another column of same data table?

xenoirita — Thu, 28 Feb 2013 06:24:40 GMT

Sorry for the confusion. I'm totally new in SAS and JMP.

excel(start) ==> JMP data table 1 ==> Fit model ==> model coefficient (from parameter estimate) ==> make into combined data table ==> JMP data table 2 consist of 3 types data ie. (a)Name (b)random number (c)random number*model coeff ==> JMP bivarate plot(end)

yes - i would like to make 6 rows data out of 3 rows.

Data table 2 - Before , what I have (3 rows):

--------------------------------------

Name Random1 RandomA2 Name RandomA1*Model Coeff1 RandomA2*Model Coeff2

col1 col2 col3 col4 col5 col6

11 12 13 14 55 56

21 22 23 24 65 66

31 32 33 34 75 76

Data table 2 - After, what I want (6 rows):

-----------------------------

col1 col2 col3 col4 col5 col6

11 12 13

21 22 23

31 32 33

54 55 56

64 65 66

74 75 76

Re: How to move a column and append it to another column of same data table?

art297 — Thu, 28 Feb 2013 13:59:16 GMT

Not sure I know what you are trying to do. If it is simply to expand the 6 columns over 2 records, then the following might do what you want. You didn't indicate why col4 gets increased by 40, but that is part of the following code:

data have;

input Name Random1 RandomA2 Name2

RandomA1_Model_Coeff1 RandomA2_Model_Coeff2;

cards;

11 12 13 14 55 56

21 22 23 24 65 66

31 32 33 34 75 76

;

data want (keep=col:);

set have;

array col(3) col1-col3;

array have(*) Name--RandomA2_Model_Coeff2;

do i=0 to 1;

do j=1 to 3;

col(j)=have(j+i*3);

if j eq 1 and i eq 1 then col(j)=col(j)+40;

end;

output;

end;

run;

Re: How to move a column and append it to another column of same data table?

jaredp — Thu, 28 Feb 2013 15:39:39 GMT

I like Art's solution. Another option is to split the data into 2 data sets then use proc append. This solution might be memory intensive though?

This uses the same data set in Art's response.

data have_split; set have;
    keep Name2        RandomA1_Model_Coeff1     RandomA2_Model_Coeff2; 
    rename Name2=Name   RandomA1_Model_Coeff1=Random1      RandomA2_Model_Coeff2= RandomA2; 
run;

data have;set have;
    drop Name2  RandomA1_Model_Coeff1     RandomA2_Model_Coeff2; 
run;

proc append base=have data=have_split; quit;

Re: How to move a column and append it to another column of same data table?

Cynthia_sas — Thu, 28 Feb 2013 16:46:58 GMT

Hi:

Do you have JMP (and only JMP) or do you have SAS -and- JMP???? If you ONLY have JMP, then you should post your question in the JMP forum. If you have SAS and JMP, then one of the posted solutions should work for you.

cynthia