Help using Base SAS procedures

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

Reply
Occasional Contributor
Posts: 9

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

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

PROC Star
Posts: 7,363

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

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.

SAS Super FREQ
Posts: 8,743

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

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

Occasional Contributor
Posts: 9

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

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


PROC Star
Posts: 7,363

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

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=colSmiley Happy;

  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;

Contributor
Posts: 71

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

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;
SAS Super FREQ
Posts: 8,743

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

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

Ask a Question
Discussion stats
  • 6 replies
  • 1228 views
  • 1 like
  • 4 in conversation