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
- /
- SAS Procedures
- /
- How to move a column and append it to another colu...

Topic Options

- Subscribe to 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
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

02-24-2013 08:23 AM

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);

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

Posted in reply to xenoirita

02-24-2013 11:40 AM

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.

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

Posted in reply to xenoirita

02-24-2013 10:50 PM

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

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

Posted in reply to Cynthia_sas

02-28-2013 01:24 AM

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**

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

Posted in reply to xenoirita

02-28-2013 08:59 AM

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;

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

Posted in reply to art297

02-28-2013 10:39 AM

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;

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

Posted in reply to xenoirita

02-28-2013 11:46 AM

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