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,
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
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
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: