DATA Step, Macro, Functions and more

Question about iteration/nested loop

Reply
Occasional Contributor
Posts: 6

Question about iteration/nested loop

2013-01-18_161730.jpg

Greetings,

I am posting here because I am completely as a loss for how to accomplish the task I need to do under the scheme of SAS' programming structure.

My problem (with an example table which is similar to my working table above) is the following:

I have certain records with a recurring ID number, in this case 1-4 as seen above. Each record has a corresponding area. For each group of numbers 1-4, I would like to be able to divide the corresponding areas for  ID's 1-3 by the areas for ID 4.

I would like then to be able to either one of the following:

a. (The more roundabout option) Create a new field where for every instance of of ID4 the corresponding Area is assigned in the new field for the id's, making division easier:

ID     AREA     NEW_FIELD

1     101.2     789.1

2     324.5     789.1

3     23.6     789.1

4     789.1

1     234.9     23.6

2     492.8     23.6   

3     153.9     23.6

4     23.6

b. the more direct and preferred option would be to bypass the previous step and just create a field outright with the computation I need.

A modified version of what I have right now (which only addresses the step mentioned in part a) is:

data Test2;

  set Work.Test;

  do i = 1 to 3;

       do j = 1 to 4;

          if ID = 4 then result = Area;

       end;

  end;

run;

I appreciate any insight that people may have to finding a solution to this problem. Unfortunately, as i mentioned, the SAS structure just isn't clicking.

earl

Respected Advisor
Posts: 4,927

Re: Question about iteration/nested loop

data have;
input ID AREA;
datalines;
1     101.2
2     324.5
3     23.6
4     789.1
1     234.9
2     492.8
3     153.9
4     23.6
;

data want;
set have(where=(ID=4));
div = area;
do until(ID=4);
     set have;
     ratio = area / div;
     output;
     end;

drop div;
run;


proc print; run;

PG

PG
Occasional Contributor
Posts: 6

Re: Question about iteration/nested loop

Thank you so much, PG. You have been immensely helpful.

Trusted Advisor
Posts: 1,137

Re: Question about iteration/nested loop

Hi,

I have written the following code in response to your post. hope this is what you are looking for. please let me know if this works for you.

data have;

input ID AREA;

datalines;

1     101.2

2     324.5

3     23.6

4     789.1

1     234.9

2     492.8

3     153.9

4     23.6

;

proc sort data=have;

    by id;

run;

%*-----------------------------------------------------------*;

%* to create a new_id to differentiate the

    sets of 1-4*;

%*-----------------------------------------------------------*;

data want;

    set have;

    retain new_id;

    by id;

    if first.id then new_id=1;

    else new_id+1;

run;

proc sort data=want;  

    by new_id id;

run;

%*------------------------------------------*;

%* to get onlt the last id value *;

%*------------------------------------------*;

data want_;

    set want;

    by new_id id;

    if last.new_id;

run;

%*-------------------------------------------------------*;

%* to get the final dataset, also division

    is also considered*;

%*------------------------------------------------------*;

data final(drop=new_id);   

    merge want_(in=a rename=(area=new_field)) want(in=b);

    by new_id;

    if a;

    if last.new_id then new_field=.;

    div=area/new_field;

run;

%*------------------------------------------*;

%* The End *;

%*------------------------------------------*;

Thanks,

Jagadish

Thanks,
Jag
Occasional Contributor
Posts: 6

Re: Question about iteration/nested loop

Posted in reply to Jagadishkatam

I'd like to thank you as well, Jagadish. It's always wonderful and useful to have/know more than one way of doing things!

Occasional Contributor
Posts: 6

Re: Question about iteration/nested loop

Just another question:

Say I only want to apply the division to records 1 and 2 and leave record 3 alone. How do I restrict the number of records the computation is applied to without messing up the loop?

Respected Advisor
Posts: 4,927

Re: Question about iteration/nested loop

Depends what you want exactly, you could do :

     if ID < 3 then ratio = area / div;

          else call missing(ratio);

     output;

or

     ratio = area / div;

     if ID < 3 then output;

In the first case you keep all records. In the second, you keep only records with a calculated ratio.

PG

PG
Occasional Contributor
Posts: 6

Re: Question about iteration/nested loop

Very very helpful, PG! Forgive me, but there was in fact one more calculation I forgot:

Say I wanted to divide a range of areas by another range of areas in a loop. That is, the ratio for

Area 1/Area 10

Area 2/Area 11

Area 3/ Area 12

Area 4/Area 13

...stopping at Area 4 and repeating for those ranges.

Again, thank you PG. You've been immensely helpful. I'm starting to get the hang of the language and I've been successful in writing some related else if statements. My initial guess is that I'll have to modify the statement that assigns the value for Area to div? That is, something like:

data want;

set have(where=(10 <= ID4 <= 13));

div = area;

do until(ID=4);

Respected Advisor
Posts: 4,927

Re: Question about iteration/nested loop

If your data is VERY regular, i.e. there always exists an area10-area13 sequence for each area1-area4 sequence, you could use :

data want;
set have(where=(ID in (10,11,12,13)));
div = area;

divID = ID;

set have(where=(ID in (1,2,3,4)));
ratio = area / div;

drop div;
run;

PG

PG
Occasional Contributor
Posts: 6

Re: Question about iteration/nested loop

What is the purpose of the "divID = ID" in the code, PG? The code works, but I'd like to be able to see all of my records (let's say say I have 20 repeating ids). Thank you so much for the help.

Ask a Question
Discussion stats
  • 9 replies
  • 309 views
  • 0 likes
  • 3 in conversation