topic Question about iteration/nested loop in SAS Programming

Question about iteration/nested loop

emedina — Fri, 18 Jan 2013 22:34:48 GMT

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;

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

Re: Question about iteration/nested loop

PGStats — Sat, 19 Jan 2013 02:46:53 GMT

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;

Re: Question about iteration/nested loop

Jagadishkatam — Sat, 19 Jan 2013 11:30:45 GMT

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

Re: Question about iteration/nested loop

emedina — Sun, 20 Jan 2013 00:42:09 GMT

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

Re: Question about iteration/nested loop

emedina — Sun, 20 Jan 2013 00:42:51 GMT

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!

Re: Question about iteration/nested loop

emedina — Sun, 20 Jan 2013 01:40:04 GMT

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?

Re: Question about iteration/nested loop

PGStats — Sun, 20 Jan 2013 03:48:25 GMT

Depends what you want exactly, you could do :

if ID < 3 then ratio = area / div;

else call missing(ratio);

output;

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.

Re: Question about iteration/nested loop

emedina — Sun, 20 Jan 2013 22:12:46 GMT

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);

Re: Question about iteration/nested loop

PGStats — Mon, 21 Jan 2013 02:23:09 GMT

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;

Re: Question about iteration/nested loop

emedina — Tue, 22 Jan 2013 14:53:08 GMT

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.