Help with DO loop

Accepted Solution Solved
Reply
Super Contributor
Posts: 419
Accepted Solution

Help with DO loop

Merry Christmas Everyone.

I am trying to solve the following problem. I thought I got it but then no. Thank you for your help.
I have 2 file. The original file (have) has a number of variables a1-a6. A filter file with condition on a given variable say a2.

In the original file, I want to pull out records that meet the condition in filter.

I got the program running for the first condition but it does not run to the next condition.

Thanks for helping.

HHC

data filter;
input a_name $ a_value;
datalines;
a1 5
a2 0
;

data have;
  input id target a1 a2 a3 a4 a5 a6;
  datalines;
1 0 5 9 1 0 8 1
2 1 4 0 1 1 5 0
3 1 5 1 2 3 1 1
4 1 3 3 0 2 0 6
5 0 4 0 1 7 0 0
6 0 3 3 0 9 0 3
7 1 2 1 1 2 1 2
8 0 5 0 0 3 0 4
;run;

data want;
if _n_ eq 1 then set filter;
set have;

array a(*)
a1 a2 a3 a4 a5 a6;
do i=1 to dim(a) until (found=1);
  if a_name=vname(a{i}) and a_value=a{i} then do;
   found=1;
   output;
   end;
end;
run;


Accepted Solutions
Solution
‎12-26-2013 07:35 PM
Super User
Super User
Posts: 7,066

Re: Help with DO loop

It is only reading one observation from FILTER because that is what you told it to do.  IF _N_=1 THEN ... will only run the first time SAS iterates over the DATA step.

Looks like perhaps you want to see how to perform a Cartesian product of two tables using a DATA step?

Here is one way to do that.  See if you an use a similar method for your problem.

data letters;

  do ch='A','B','C'; output; end;

run;

data numbers;

  do x=1 to 3; output; end;

run;

data both ;

  set letters ;

  do i = 1 to nobs ;

    set numbers point=i nobs=nobs ;

    output;

    put ch= x=;

  end;

run;


ch=A x=1

ch=A x=2

ch=A x=3

ch=B x=1

ch=B x=2

ch=B x=3

ch=C x=1

ch=C x=2

ch=C x=3

View solution in original post


All Replies
Frequent Contributor
Posts: 106

Re: Help with DO loop

Hi, Don't know whether i have understood your problem but if you want to subset dataset HAVE on basis of dataset FILTER , i.e. where a1=5 and a2=0 then here is the code you can use,, if you want something else then please give your desired output dataset.

Code:

proc sql ;

select catx('=',a_name,a_value) into :filter_value separated by ' and ' from filter  ;

select * from have where &filter_value;

quit;

Respected Advisor
Posts: 4,927

Re: Help with DO loop

Ho! Ho! Ho! No loop needed!

data filter;
input a_name $ a_value;
datalines;
a1 5
a2 0
;

proc transpose data=filter out=tfilter(drop=_name_);
var a_value;
id a_name;
run;

data have;
input id target a1 a2 a3 a4 a5 a6;
datalines;
1 0 5 9 1 0 8 1
2 1 4 0 1 1 5 0
3 1 5 1 2 3 1 1
4 1 3 3 0 2 0 6
5 0 4 0 1 7 0 0
6 0 3 3 0 9 0 3
7 1 2 1 1 2 1 2
8 0 5 0 0 3 0 4
;

proc sql;
create table want as
select * from have natural join tfilter;
select * from want;
quit;

Merry Christmas to you!

PG

PG
Super Contributor
Posts: 419

Re: Help with DO loop

Thanks Santa for sending helps using SQL :-)

Actually, I feel so dump not being able to do the do loop since my code can only read the first conditions without moving to the next one.

Can you guys check that Do please?

Thank you,

HHC

Respected Advisor
Posts: 4,927

Re: Help with DO loop

For the Do loop approach to work, you would need three arrays. One for the a_name values, one for the a_value values and one for a1-a6. On iteration 1 you would read in the first two arrays from dataset filter. You would then need either an inner loop to compare each a_name with each variable name or to use the whichc function. I'm getting tired, just describing it.

One thing is certain, it would be more complicated than the two SQL-based solutions proposed.

PG

PG
Occasional Contributor
Posts: 18

Re: Help with DO loop

HI,

Just a thought, what about Hashes instead of the proc sql. I guess might be 2 hash tables perhaps? do you reckon would it work?

Sorry if it didn.t make sense,

Naveen

Frequent Contributor
Posts: 106

Re: Help with DO loop

Hi Merry Christmas to you !!!

Hopefully SQL has answered your question...and why to use array's and loops when it can be done by simple SQL's.

Pradeep

Super Contributor
Posts: 419

Re: Help with DO loop

Posted in reply to pradeepalankar

Hi,

The reason I have to use the Do loop is that if I have few condition applied at once, sql will not be as convenient.

I am still struggling with the Do Loop in SAS.

Thank you,

HHC

Super User
Super User
Posts: 7,066

Re: Help with DO loop

Since SAS is an interpreted language this type of problem is probably easier to solve with code generation.  Use your filter criteria to generate code.

If the file is small then you can generate the code into macro variable using PROC SQL.

proc sql noprint;

select catx('=',a_name,a_value) into :where separated by ' or ' from filter ;

create table want as select * from have where &where ;

quit;

If it is large enough that it would not fit into a single macro variable then you will need another method. A simple one is to use the DATA step's report writing tools to write the code.

filename code temp;

data _null_;

  set filter end=eof ;

  file code ;

  if _n_=1 then put 'where ' @;

  else put ' or ' @;

  put '(' a_name '=' a_value ')' ;

  if eof then put ';'

run;

data want ;

set have;

%inc code ;

run;

Super Contributor
Posts: 419

Re: Help with DO loop

Thank you,Tom, PGStats and NaveenSrinivasan for your help.

As you might remember I try to learn that  Doo-loop from time to time on the forum.

I think my problem is almost solve. The obstacle I have right now is that SAS ONLY read the first record of the filter file and process it (correctly). I must miss one thing somewhere in the code.

"NOTE: There were 1 observations read from the data set WORK.FILTER"

Could you please run this code with 2 conditions and help me?

Many thanks as always.

HHC

data filter;
input a_name $ a_value b_name $ b_value;
datalines;
a1 2 a2 3
a2 0 a4 7
;

data have;
  input id target a1 a2 a3 a4 a5 a6;
  datalines;
1 0 5 9 1 0 8 1
2 1 4 0 1 1 5 0
3 1 2 3 2 3 1 1
4 1 3 3 0 2 0 6
5 0 4 0 1 7 0 0
6 0 2 3 0 9 0 3
7 1 2 1 1 2 1 2
8 0 5 0 0 3 0 4
;run;


data want;
if _n_ eq 1 then set filter ;
set have;
array a(*) a1 a2 a3 a4 a5 a6;
do i=1 to dim(a) ;
  do j=1 to dim(a);
  if a_name=vname(a{i}) and a_value=a{i} and b_name=vname(a{j}) and b_value=a{j} then do;
  found=1;
   output;
   end;
end;
end;
run;

Solution
‎12-26-2013 07:35 PM
Super User
Super User
Posts: 7,066

Re: Help with DO loop

It is only reading one observation from FILTER because that is what you told it to do.  IF _N_=1 THEN ... will only run the first time SAS iterates over the DATA step.

Looks like perhaps you want to see how to perform a Cartesian product of two tables using a DATA step?

Here is one way to do that.  See if you an use a similar method for your problem.

data letters;

  do ch='A','B','C'; output; end;

run;

data numbers;

  do x=1 to 3; output; end;

run;

data both ;

  set letters ;

  do i = 1 to nobs ;

    set numbers point=i nobs=nobs ;

    output;

    put ch= x=;

  end;

run;


ch=A x=1

ch=A x=2

ch=A x=3

ch=B x=1

ch=B x=2

ch=B x=3

ch=C x=1

ch=C x=2

ch=C x=3

Super Contributor
Posts: 419

Re: Help with DO loop

Thank you, Tom.

You save me one more time!

It is the bug

data want;
set filter ;
do x=1 to nobs;

set have point=x nobs=nobs;

I really like the Do loop since I can control the process better.

Thank you and have a Happy New Year.

HHC

data filter;
input a_name $ a_value b_name $ b_value;
datalines;
a1 2 a2 3
a2 0 a4 7
;

data have;
  input id target a1 a2 a3 a4 a5 a6;
  datalines;
1 0 5 9 1 0 8 1
2 1 4 0 1 1 5 0
3 1 2 3 2 3 1 1
4 1 3 3 0 2 0 6
5 0 4 0 1 7 0 0
6 0 2 3 0 9 0 3
7 1 2 1 1 2 1 2
8 0 5 0 0 3 0 4
;run;

data want;
set filter ;
do x=1 to nobs;

set have point=x nobs=nobs;
array a(*) a1 a2 a3 a4 a5 a6;
do i=1 to dim(a) ;
  do j=1 to dim(a);
  if a_name=vname(a{i}) and a_value=a{i} and b_name=vname(a{j}) and b_value=a{j} then do;
  found=1;
   output;
   end;
end;
end;
end;
run;

Super User
Super User
Posts: 7,066

Re: Help with DO loop

Depending on what question you are trying to answer you might want to reverse the position of the FILTER and HAVE datasets.

Using FILTER as the first SET statement is answering the question "Which observations match this filter?".

Reverse it an you can ask the question "Which filters does this observation match?".

Super Contributor
Posts: 419

Re: Help with DO loop

Oh, I see.

I'll try to play around with it.

HHC

🔒 This topic is solved and locked.

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

Discussion stats
  • 13 replies
  • 435 views
  • 4 likes
  • 5 in conversation