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);
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.
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
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
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;
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;
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
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.