Help need for Macro or running embeded codes

Accepted Solution Solved
Reply
Super Contributor
Posts: 371
Accepted Solution

Help need for Macro or running embeded codes

Good morning Everyone,

I have the following problem and I am not sure how to put it in a short sentence.
Please have a look. Thank you for your time and help.

I have 2 files.
File Have has a target variables and a number of independent variables (aa bb cc dd).
File Cond has these independent variables. Each row in this file is a condition.

What I want to do is that:


Take the first row in file Cond, Sql with file Have to create dataset cond1_subsample, which contain value of Target

  Process the cond1_subsample....
  Process the cond1_subsample ...
  Process the cond1_subsample ...
   (this above processing step could be A code section or seperate code)

  Get result to Resultfile

Go to the next record in file Cond and do the same.

I am not sure how to setup this iteration and I really appreciate it if you could help me.

Thank you,

HHC


data have;
  input target aa bb cc dd;
  datalines;
0 1 9 1 0
0 1 0 1 1
0 1 9 2 3
1 2 3 0 2
0 2 9 1 7
0 3 3 0 9
1 2 1 1 2
0 2 2 0 3
;;;;
run;

data cond;
input aa bb cc dd;
datalines;
1 1 . .
2 1 9 .
2 2 . 0
;;
run;

data cond; set cond;
id=_n_;run;


*the merging condition is OR as below;
proc sql;
create table out
as select a.*, b.target
from cond as a left join have as b
on a.aa=b.aa or a.bb=b.bb or a.cc=b.cc or a.dd=b.dd; quit;

proc sort; by id;run;


Accepted Solutions
Solution
‎01-30-2014 12:54 PM
Occasional Contributor
Posts: 12

Re: Help need for Macro or running embeded codes

*There are a few ways you could set up a loop.  First find out how many records are in your first table:

proc sql noprint;

select count(*) into :row_ct

from have;

quit;

*Then setup a macro loop;

%macro myloop();

%do i = 1 %to &row_ct;

     data cond&i;

     set cond;

     if _n_ = &i;

     id = _n_;

     run;

     proc sql;

     create table cond&i._subsample

     as select a.*, b.target

     from cond&i as a left join have as b

     on a.aa=b.aa or a.bb=b.bb or a.cc=b.cc or a.dd=b.dd; quit;

     proc sort; by id;run;

%end;

%mend;

%myloop;

View solution in original post


All Replies
Solution
‎01-30-2014 12:54 PM
Occasional Contributor
Posts: 12

Re: Help need for Macro or running embeded codes

*There are a few ways you could set up a loop.  First find out how many records are in your first table:

proc sql noprint;

select count(*) into :row_ct

from have;

quit;

*Then setup a macro loop;

%macro myloop();

%do i = 1 %to &row_ct;

     data cond&i;

     set cond;

     if _n_ = &i;

     id = _n_;

     run;

     proc sql;

     create table cond&i._subsample

     as select a.*, b.target

     from cond&i as a left join have as b

     on a.aa=b.aa or a.bb=b.bb or a.cc=b.cc or a.dd=b.dd; quit;

     proc sort; by id;run;

%end;

%mend;

%myloop;

Super Contributor
Posts: 371

Re: Help need for Macro or running embeded codes

That's awesome, Nathan.

It is very easy to follow.

Thank you very much.

HHC

Occasional Contributor
Posts: 5

Re: Help need for Macro or running embeded codes

You could also use the call symput function.

without use of the id column

data _null_;

  set cond;

  call execute("proc sql;

                create table tmp_" || strip(_n_) || " as

                Select a.*, b.target

                from cond a left join have b

                on a.aa=b.aa or a.bb=b.bb or a.cc=b.cc or a.dd=b.dd

                where a.aa= " || aa || " and a.bb= " || bb || " and a.cc= " || cc || " and a.dd= " || dd || "

                ;quit;"

  );

run;

with id column

data _null_;

  set cond;

  call execute("proc sql;

                create table tmp_" || strip(id) || " as

                Select a.*, b.target

                from cond a left join have b

                on a.aa=b.aa or a.bb=b.bb or a.cc=b.cc or a.dd=b.dd

                where a.id= " || id ||  "

                ;quit;"

  );

run;

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 330 views
  • 0 likes
  • 3 in conversation