BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
leonzheng
Obsidian | Level 7

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,

   

1 ACCEPTED SOLUTION

Accepted Solutions
art297
Opal | Level 21

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

 

View solution in original post

2 REPLIES 2
art297
Opal | Level 21

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
Obsidian | Level 7
thx, I will try this call execute function

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 2 replies
  • 626 views
  • 0 likes
  • 2 in conversation