condition function for not so simple operation

[leonzheng]

I am doing some data pulling to form a table called table1, with a column called "number", the value of this column are all same, either "2" or "3" depending on what data I pulled. I have two JMP files called JMP2 and JMP3. Now I want to import and merge one of them with table1, depending on the value of "number".

Here is want I tried:

1. use condition to import JMP files

    data table1;

      set table1;

      if number = 2 then

      proc import out = work.table2

           datafile = 'C:\JMP2'

           dbms = JMP relace;

      if number = 3 then      

      proc import out = work.table3

           datafile = 'C:\JMP3'

           dbms = JMP relace;

   run;

2. import both JMP files (as table2 and table3, no problem), then use condition to merge

     data want;

        if number = 2 then

          merge table1 table2;

          by number;

        if number = 3 then

          merge table1 table3;

          by number;

    run;

Both are not working, it seems if and case when (I also tried) can only do simple operation like assign value and delete rows.

Is there a way to do my task?

Thanks,

[art297 · Accepted solution]

You can't run a proc from within a datastep, but you can use call execute.

And I don't understand what you're trying to merge, as all of your data records will either have number eq 1 or 2.

I think the following is similar to what you want:

/* Create test files */
  proc sort data=sashelp.class out=class;
    by name;
  run;

  data class;
    set class;
    if sex eq 'M' then number=2;
    else number=3;
  run;
  
  data jmp2 (keep=name height);
    set class (where=(sex eq 'M'));
  run;

  PROC EXPORT DATA= WORK.jmp2 
              OUTFILE= "/folders/myfolders/jmp2.jmp"
              DBMS=JMP REPLACE;   
  RUN;   

  data jmp3 (keep=name weight);
    set class (where=(sex eq 'F'));
  run;

  PROC EXPORT DATA= WORK.jmp3 
              OUTFILE= "/folders/myfolders/jmp3.jmp"
              DBMS=JMP REPLACE;   
  RUN;   

/* Create table1 for number 2 */
  data table1;
    set class(keep=name sex number where=(sex eq 'M'));
  run;

/* Run code for number eq 2 */
data _null_;
  set table1(obs=1);
  call execute('proc import out = work.temp dbms=JMP replace datafile=');
  if number eq 2 then do;
/*     call execute('C:\JMP2.jmp;run;'); */
    call execute("'/folders/myfolders/JMP2.jmp';run;");
  end;
  else if number eq 3 then do;
/*     call execute('C:\JMP3.jmp;run;'); */
    call execute("'/folders/myfolders/JMP3.jmp';run;");
  end;
run;

data want;
  merge table1 temp;
  by name;
run;

/* Create table1 for number 3 */
  data table1;
    set class(keep=name sex number where=(sex eq 'F'));
  run;

/* Run code for number eq 3 */
data _null_;
  set table1(obs=1);
  call execute('proc import out = work.temp dbms=JMP replace datafile=');
  if number eq 2 then do;
/*     call execute('C:\JMP2.jmp;run;'); */
    call execute("'/folders/myfolders/JMP2.jmp';run;");
  end;
  else if number eq 3 then do;
/*     call execute('C:\JMP3.jmp;run;'); */
    call execute("'/folders/myfolders/JMP3.jmp';run;");
  end;
run;

data want;
  merge table1 temp;
  by name;
run;

Art, CEO, AnalystFinder.com

[leonzheng]

thx, I will try this call execute function