nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

joining two sheets and chaning the format of the resultant sheet

unknown

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

Accepted answers

pmroz

dt = data table("Final Sheet");

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

All comments

kevinmc

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;

unknown

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?

pmroz

dt = data table("Final Sheet");

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

unknown

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?