Choose Language Hide Translation Bar
Highlighted
fr2007
Level II

joining two sheets and chaning the format of the resultant sheet


hi all

iam trying to join two excel sheets(i have a common column in both the sheets) using Join command in JMP. For the resultant sheet (FInal. xlsx), I need to change the format of one particular column from chanracter to numeric. I want to include this in my script so that if I run the script, I get joined excel sheet with the column format changed

ex:

Data Table( "Sheet1" )

<< Join(

With( Data Table( "Data" ) ),

By Matching Columns( :Name= :Name2),

Drop multiples( 1, 1 ),

Name( "Include non-matches" )(0, 0),

Preserve main table order( 1 ),

Output Table( "Final Sheet" )

)

in the final sheet, i want to change the format of the column "D" from character to numeric

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
pmroz
Super User

Re: joining two sheets and chaning the format of the resultant sheet

dt = data table("Final Sheet");

column(dt, "D") << data type(numeric) << analysis type(continuous);

View solution in original post

7 REPLIES 7
Highlighted
kevinmc
Level I

Re: joining two sheets and chaning the format of the resultant sheet

in base sas:

data data1(rename=(name2=name)); /*here variable name2 change to name why because we need common variable while joining*/

set data;

run;

data finalsheet;

merge sheet1 data1;

by name;

dd=input(d,9.);/* by using input function we can convert character to numeric*/

run;

Highlighted
fr2007
Level II

Re: joining two sheets and chaning the format of the resultant sheet

i am trying to write a script in JSL..i performed the join function using the tables->join functionality and just copied the script from the script editor. Now I want to change the format of one of the columns in the resultant sheet. From Character to Numeric. How do I include this?

Highlighted
pmroz
Super User

Re: joining two sheets and chaning the format of the resultant sheet

dt = data table("Final Sheet");

column(dt, "D") << data type(numeric) << analysis type(continuous);

View solution in original post

Highlighted
fr2007
Level II

Re: joining two sheets and chaning the format of the resultant sheet

Thank you so much. I want to add a small piece of code to the resultant code..i want to take out all the rows that have the numeric value of '0' in the column D;

ex:

Data Table( "Sheet1" )

<< Join(

With( Data Table( "Data" ) ),

By Matching Columns( :Name= :Name2),

Drop multiples( 1, 1 ),

Name( "Include non-matches" )(0, 0),

Preserve main table order( 1 ),

Output Table( "Final Sheet" )

);

dt = data table("Final Sheet");

column(dt, "D") << data type(numeric) << analysis type(continuous);

dt = data table("Final Sheet");

match_rows = dt << get rows where(contains(:D, "0"));

newdt = dt << subset(columns(), rows(match_rows));

but iam unable to get the rows that has the numeric value of 0 in the column D and then subtract the value of column E from Column C (which are in date format mm/dd/yyyy)..can you please help me with this code?

Highlighted
pmroz
Super User

Re: joining two sheets and chaning the format of the resultant sheet

Since D is now numeric you can't compare it to a string ("0").  This code will work:

match_rows = dt << get rows where( :D == 0 );

dt << delete rows(match_rows);

Highlighted
fr2007
Level II

Re: joining two sheets and chaning the format of the resultant sheet

but this code gives me rows that have the numeric value of 0 in column D. But it is not giving the difference between Column E and Column C (which are in date format). The difference between the two days should be displayed in another new column(along with all the columns in the "Final Sheet").

Highlighted
fr2007
Level II

Re: joining two sheets and chaning the format of the resultant sheet

but this code gives me rows that have the numeric value of 0 in column D. But it is not giving the difference between Column E and Column C (which are in date format). The difference between the two days should be displayed in another new column(along with all the columns in the "Final Sheet").