BookmarkSubscribeRSS Feed
emedina
Calcite | Level 5

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

9 REPLIES 9
PGStats
Opal | Level 21

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
emedina
Calcite | Level 5

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

Jagadishkatam
Amethyst | Level 16

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
emedina
Calcite | Level 5

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!

emedina
Calcite | Level 5

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?

PGStats
Opal | Level 21

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
emedina
Calcite | Level 5

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

PGStats
Opal | Level 21

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
emedina
Calcite | Level 5

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 9 replies
  • 1119 views
  • 0 likes
  • 3 in conversation