Help Needed

[Rhino84]

Hi Friends,

I need help with designing the below code. 

Consider the table below. I need to derive the last column.

The only way that i can do that is.

I have multiple groups as given below and each group has different ID's associated with them. 

The interest rate of different groups are given. To derive the NEW interest rate, i need to select INTEREST RATE OF only those ID's that have a value of 300 or 500 (a group can have either 300 or 500 value). The Interest rate of  either 300 or 500 ID becomes the NEW rate of that group.

I am not able to code this logic. Please help

Group	ID	Interest rate	TO BE DERIVED: New Rate
x	300	12	12
	700	1	12
	701	2	12
	702	3	12
	703	3	12
	704	4	12
y	300	15	15
	700	4	15
	701	5	15
	702	6	15
	703	7	15
	704	6	15
z	500	23	23
	700	4	23
	701	3	23
	702	2	23
	703	1	23
	704	4	23
z1	500	20	20
	700	5	20
	701	8	20
	702	9	20
	703	6	20
	704	3	20

[Rhino84]

Hi Friends,

I need help with designing the below code. 

Consider the table below. I need to derive the last column.

The only way that i can do that is.

I have multiple groups as given below and each group has different ID's associated with them. 

The interest rate of different groups are given. To derive the NEW interest rate, i need to select INTEREST RATE OF only those ID's that have a value of 300 or 500 (a group can have either 300 or 500 value). The Interest rate of  either 300 or 500 ID becomes the NEW rate of that group.

I am not able to code this logic. Please help

Group	ID	Interest rate	TO BE DERIVED: New Rate
x	300	12	12
	700	1	12
	701	2	12
	702	3	12
	703	3	12
	704	4	12
y	300	15	15
	700	4	15
	701	5	15
	702	6	15
	703	7	15
	704	6	15
z	500	23	23
	700	4	23
	701	3	23
	702	2	23
	703	1	23
	704	4	23
z1	500	20	20
	700	5	20
	701	8	20
	702	9	20
	703	6	20
	704	3	20

[Reeza]

FYI - I merged your posts into a single post. In the future please do not post the same question multiple times. Please also use a descriptive subject line in the future, and not all caps.

[Reeza]

300/500 also seem to be the lowest values in the data set. Will that always hold true?

If so, something like this works:

Note that I'm assuming that your group identifier is actually repeated across each row, not blank as in your sample data. 

data want;
set have;
by group;
retain new_rate;
if first.ID then new_rate = interest_rate;
run;

If it was blank....

data want;
set have;
retain new_rate;
if not missing(Group) then new_rate = interest_rate;
run;

---

@Rhino84 wrote:

Hi Friends,

I need help with designing the below code. 

Consider the table below. I need to derive the last column.

The only way that i can do that is.

I have multiple groups as given below and each group has different ID's associated with them. 

The interest rate of different groups are given. To derive the NEW interest rate, i need to select INTEREST RATE OF only those ID's that have a value of 300 or 500 (a group can have either 300 or 500 value). The Interest rate of  either 300 or 500 ID becomes the NEW rate of that group.

I am not able to code this logic. Please help

Group	ID	Interest rate	TO BE DERIVED: New Rate
x	300	12	12
	700	1	12
	701	2	12
	702	3	12
	703	3	12
	704	4	12
y	300	15	15
	700	4	15
	701	5	15
	702	6	15
	703	7	15
	704	6	15
z	500	23	23
	700	4	23
	701	3	23
	702	2	23
	703	1	23
	704	4	23
z1	500	20	20
	700	5	20
	701	8	20
	702	9	20
	703	6	20
	704	3	20

---

[Rhino84]

Its basically 3000 or 5000. This value will never change. ##- Please type
your reply above this line. No attachments. -##
--
Thanks and Regards,
Rajat Peer

[Rhino84]

data want;
set have;
by group;
retain new_rate;
if ID in ('300','500') then new_rate = interest_rate;
run;

This Seems to make the corresponding value in the new column same as the interest rate. BUt how do i update rest of values 700 through 7004 with the same value of interest rate?

[Reeza]

RETAIN Should be doing that automatically until you get to the next 300/500. However, if the 300/500 come AFTER the 700 it won't get updated.

[Rhino84]

300 0r 500 is the second value in a group, it comes after 100,(300 or 500), 700,701,702,703,704 and 705 is the correct order and the vale of new column can be either 300 or 500 and it will replicate for all ID's.

Please help.I have been stuck on this for 3 days

[jimbarbour]

I don't know what your "100" level records look like, but try running your data through something like this, below.  You don't have to run the first Data step if already have a SAS data set.

Jim

DATA	Have;
	LENGTH	Group			$2;
	LENGTH	ID				$3;
	LENGTH	Interest_Rate	8.;
	RETAIN	Group			' ';
	INFILE	Datalines		DSD	DLM='09'X	MISSOVER;
	INPUT	Group			$
			ID				$
			Interest_Rate
			;
DATALINES;
x	300	12
 	700	1
 	701	2
 	702	3
 	703	3
 	704	4
y	300	15
 	700	4
 	701	5
 	702	6
 	703	7
 	704	6
z	500	23
 	700	4
 	701	3
 	702	2
 	703	1
 	704	4
z1	500	20
 	700	5
 	701	8
 	702	9
 	703	6
 	704	3
;
RUN;

DATA	Want;
	RETAIN	_Prior_Group	'  ';
	DROP	_Prior_Group;
	SET	Have;
	RETAIN	New_Rate		0;
	IF	MISSING(Group)			THEN
		Group		=	_Prior_Group;
	_Prior_Group	=	Group;
	IF	ID			=	'300'	OR
		ID			=	'500'	THEN
		New_Rate	=	Interest_Rate
		;
RUN;

[Reeza]

You have a few answers now that will solve this issue but in general, this means your example data is not reflective of your actual situation. Make sure to spend some more time ensuring your examples match your actual data.

---

@Rhino84 wrote:
300 0r 500 is the second value in a group, it comes after 100,(300 or 500), 700,701,702,703,704 and 705 is the correct order and the vale of new column can be either 300 or 500 and it will replicate for all ID's.

Please help.I have been stuck on this for 3 days

---

[mkeintz · Accepted solution]

Since:

1. You apparently have exactly 1  record with ID=300 or 500 per group
2. Data are sorted by group

then you can make your task into a simple one-to-many merge:

data want;
  merge have (keep=group id interest_rate where=(id=300 or id=500) rename=interest_rate=new_rate)
        have ;
  by group;
run;

BTW, this allows the ID=300/500 record to fall anywhere in its group - it doesn't have to be first.

Let SAS do the work.

Postscript: Added "group" to the "keep=" parameter.

[Rhino84]

The below table reflects the correct data.

The below query helps me to make the new rate same as interest rate. But i want all the corresponding rows in New rate be same as Interest rate of 300 or 500 to be same within each single group.

Group	ID	Interest rate	TO BE DERIVED: New Rate
x	300	12	12
x	700	1
x	701	2
x	702	3
x	703	3
x	704	4
y	300	15	15
y	700	4
y	701	5
y	702	6
y	703	7
y	704	6
z	500	23	23
z	700	4
z	701	3
z	702	2
z	703	1
z	704	4
z1	500	20	20
z1	700	5
z1	701	8
z1	702	9
z1	703	6
z1	704	3

[Rhino84]

This gives me the following o/p as above.

[Reeza]

Please show us the exact code you ran on the sample data above. The merge solution should work regardless.

[Rhino84]

data want;
set have;
by group;
reatin new rate;
if ID in ('300','500') then new_rate= Interest_rate;
run;
this code gives the below o/p;
Group ID Interest rate TO BE DERIVED: New Rate
x 300 12 12
x 700 1
x 701 2
x 702 3
x 703 3
x 704 4
y 300 15 15
y 700 4
y 701 5
y 702 6
y 703 7
y 704 6
z 500 23 23
z 700 4
z 701 3
z 702 2
z 703 1
z 704 4
z1 500 20 20
z1 700 5
z1 701 8
z1 702 9
z1 703 6
z1 704 3

i need rest of the rows for new rate to be same as the rate selected. this is my desired o/p that i want.

Group ID Interest rate TO BE DERIVED: New Rate
x 300 12 12
x 700 1 12
x 701 2 12
x 702 3 12
x 703 3 12
x 704 4 12
y 300 15 15
y 700 4 15
y 701 5 15
y 702 6 15
y 703 7 15
y 704 6 15
z 500 23 23
z 700 4 23
z 701 3 23
z 702 2 23
z 703 1 23
z 704 4 23
z1 500 20 20
z1 700 5 20
z1 701 8 20
z1 702 9 20
z1 703 6 20
z1 704 3 20