5498_en-US

liliconcom

joining two sheets and chaning the format of the resultant sheet<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />

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

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").

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").

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

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?

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

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?

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;