Help with do loop reading only 1 record

Reply
Super Contributor
Posts: 371

Help with do loop reading only 1 record

Hi Everyone,

I have 2 datasets. "Original" data with var1-var5. "Filter" data has variable name and value.

I want to find for each filter, how many record in Original meet the filter criteria.

I write a Do loop code to count the number of time criteria is met. This code read ONLY first record in the filter file and process correctly.

Could you fix this code so that I will run all data in the filter file (which should create 8*9=72 records in Want file)?

Thank you so much.

HHC

                                                                                                                                       
data original;                                                                                                                         
input id target var1 var2 var3 var4 var5;                                                                                              
datalines;                                                                                                                             
1 0 0 1 2 4 0                                                                                                                          
2 0 0 1 2 4 1                                                                                                                          
3 1 0 1 4 6 0                                                                                                                          
4 1 0 8 6 9 8                                                                                                                          
5 1 1 3 6 8 11                                                                                                                         
6 1 8 6 5 3 4                                                                                                                          
7 0 9 6 5 3 4                                                                                                                          
8 2 9 6 5 3 4                                                                                                                          
9 4 9 6 5 3 4                                                                                                                          
;                                                                                                                                      
                                                                                                                                       
data filter;                                                                                                                           
input filter_id cond1 $ value1 cond2 $ value2;                                                                                         
datalines;                                                                                                                             
1 var1 0 var2 1                                                                                                                        
2 var1 0 var5 0                                                                                                                        
3 var2 1 var4 4                                                                                                                        
5 var1 3 var5 4                                                                                                                        
6 var2 1 var4 4                                                                                                                        
7 var4 3 var5 4                                                                                                                        
8 var1 3 var5 4                                                                                                                        
;                                                                                                                                      
run;                                                                                                                                   
                                                                                                                                       
                                                                                                                                                                                                                    
*somehow it only read 1 record in filter;                                                                                              
data want;                                                                                                                             
if  _n_ eq 1 then set filter;                                                                                                          
                                                                                                                                       
array name1{2} cond1 cond2;                                                                                                            
array val{2} value1 value2;                                                                                           

                                                                                                                          
array org_var{5} var1 var2 var3 var4 var5;                                                                                           
  match=0;                                                                                                                             
                                                                                                                                       
  set original;                                                                                                                        
                                                                                                                                       
  do i=1 to 2;                                                                                                                         
  do j=1 to 5;                                                                                                                         
                                                                                                                                       
  if name1{i}=vname(org_var{j}) and val{i}=org_var{j} then match=match+1;                                                              
                                                                                                                                       
  end;                                                                                                                                 
  end;

*if match  <2 then delete;                                                                                                                               
run;                                                                                                                                   
                                                                                                                                       
                                                                                                                                       
                                                          
                                                                                                                                   
                                                                                                                                      

Super User
Super User
Posts: 6,495

Re: Help with do loop reading only 1 record

Sounds like for each record in ORIGINAL you want to examine every record in FILTERS. So your skeleton data step will look like this.

data want ;

  set original ;

...

  do p=1 to nobs ;

    set filter point=p nobs=nobs ;

   .....

end;

...

run;

Super Contributor
Posts: 371

Re: Help with do loop reading only 1 record

Hi Tom,

I follow you suggestion as below but it doesn't work the way I want.

As each filter has 2 condition, I want to check for each filter, each record in original file, how many condition is met.
So the final file should have 7*9 records with below format:
Filter| Original_record| Number_match
1 1  2
1 2  1
1 ..
..
1 8  0
1 9  ..
2 1  ..
2 2  
.. ..

Could you please check if my original or new wrong anywhere?

Thanks, Tom.

HHC
                                                                                                                                       
data want;                                                                                                                             
  set original;                                                                                                                        
  array org_var{5} var1 var2 var3 var4 var5;                                                                                           
  match=0;                                                                                                                             
                                                                                                                                       
  do p=1 to nobs;                                                                                                                      
     set filter point=p nobs=nobs;                                                                                                     
     array name1{2} cond1 cond2;                                                                                                       
     array val{2} value1 value2;                                                                                                       
                                                                                                                                       
        do i=1 to 2;                                                                                                                   
        do j=1 to 5;                                                                                                                   
                                                                                                                                       
                                                                                                                                       
  if name1{i}=vname(org_var{j}) and val{i}=org_var{j} then match=match+1;                                                              
        end;                                                                                                                           
        end;                                                                                                                           
  end;                                                                                                                                 
                                                                                                                                       
run;                                                                                                                                   

Super User
Super User
Posts: 6,495

Re: Help with do loop reading only 1 record

I think you want to count how many of the different filters matched by each observation in original.

So you need one variable to keep track of did it match the current filter it is checking and other to keep track of the number of filters that it matched.

data original;

input id target var1 var2 var3 var4 var5;

datalines;

1 0 0 1 2 4 0

2 0 0 1 2 4 1

3 1 0 1 4 6 0

4 1 0 8 6 9 8

5 1 1 3 6 8 11

6 1 8 6 5 3 4

7 0 9 6 5 3 4

8 2 9 6 5 3 4

9 4 9 6 5 3 4

;

data filter;

input filter_id cond1 $ value1 cond2 $ value2;

datalines;

1 var1 0 var2 1

2 var1 0 var5 0

3 var2 1 var4 4

5 var1 3 var5 4

6 var2 1 var4 4

7 var4 3 var5 4

8 var1 3 var5 4

;

run;

data want;

  set original;

  array var var1-var5;

  do p=1 to nobs;

    set filter point=p nobs=nobs;

    array cond cond1 cond2;

    array value value1 value2;

    match=0;

    do i=1 to dim(cond) while (match=0);

      do j=1 to dim(var) while (match=0);

        if upcase(cond(i))=upcase(vname(var(j)))

           and value(i)=var(j) then match=1;

      end;

    end;

    nmatch=sum(nmatch,match);

  end;

  put (id nmatch) (=);

run;


id=1 nmatch=4

id=2 nmatch=4

id=3 nmatch=4

id=4 nmatch=2

id=5 nmatch=0

id=6 nmatch=3

id=7 nmatch=3

id=8 nmatch=3

id=9 nmatch=3

Super Contributor
Posts: 371

Re: Help with do loop reading only 1 record

Hi Tom,

I work on your suggestions again and add the "output" in. It works.

My explaination is not good so I make you misunderstood the problem somehow.

Anyway, I finally was able to get it done in BOTH way as below.

I still have one more step on top of this one to complete my problem. I will try to solve it first and hopefully I can get it.

I appreciate your help.

HHC

*Method 1;

data want;                                                                                                                             

drop i j;                                                                                                                              

  set original;                                                                                                                        

  array org_var{5} var1 var2 var3 var4 var5;                                                                                           

                                                                                                                                       

  do p=1 to nobs;                                                                                                                      

  match=0;                                                                                                                             

     set filter point=p nobs=nobs;                                                                                                     

     array name1{2} cond1 cond2;                                                                                                       

     array val{2} value1 value2;                                                                                                       

                                                                                                                                       

        do i=1 to 2;                                                                                                                   

        do j=1 to 5;                                                                                                                   

                                                                                                                                

  if name1{i}=vname(org_var{j}) and val{i}=org_var{j} then match=match+1;                                                              

        end;                                                                                                                           

        end;                                                                                                                           

output;                                                                                                                                

  end;                                                                                                                                 

run;                                                                                                                                   

proc sort data=want; by filter_id id;run;                                                                                              

                                                                                                                                       

                                                                                                                                       

*Method 2;                                                                                                                                       

data want3;                                                                                                                            

drop i j;                                                                                                                              

  set filter;                                                                                                                          

     array name1{2} cond1 cond2;                                                                                                       

     array val{2} value1 value2;                                                                                                       

                                                                                                                                       

  do p=1 to nobs;                                                                                                                      

  match=0;                                                                                                                             

     set original point=p nobs=nobs;                                                                                                   

        array org_var{5} var1 var2 var3 var4 var5;                                                                                     

                                                                                                                                       

        do i=1 to 2;                                                                                                                   

        do j=1 to 5;                                                                                                                   

                                                                                                                                       

                                                                                                                                       

  if name1{i}=vname(org_var{j}) and val{i}=org_var{j} then match=match+1;                                                              

        end;                                                                                                                           

        end;                                                                                                                           

        output;                                                                                                                        

  end;                                                                                                                                 

                                                                                                                                       

run;

New Contributor
Posts: 4

Re: Help with do loop reading only 1 record

I counted the number of original observetions for each filter criteria.

proc sql;

  create table A1 as select * from original, filter;

  run;

data A2;

  set A1;

  array VV{5}    var1-var5;

  if VV{input(compress(cond1,"var"),best.)}=value1 &

     VV{input(compress(cond2,"var"),best.)}=value2;

  run;

proc sort data=A2;

  by filter_id;

  run;

data A3(keep=filter_id cond: value: count);

  set A2;

  by filter_id;

  if first.filter_id then count=0;

  count+1;

  if last.filter_id;

  run;

data A4;

  merge filter A3;

  by filter_Id;

  if count=. then count=0;

  run;

proc print data=A4;

  run;

Ask a Question
Discussion stats
  • 5 replies
  • 221 views
  • 0 likes
  • 3 in conversation