topic Re: Help Needed in SAS Programming

Help Needed

Rhino84 — Tue, 27 Jul 2021 21:56:44 GMT

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

HELP

Rhino84 — Tue, 27 Jul 2021 21:59:26 GMT

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.

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

Re: Help Needed

Reeza — Tue, 27 Jul 2021 22:11:50 GMT

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

Re: HELP

Reeza — Tue, 27 Jul 2021 22:14:20 GMT

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.

Re: Help Needed

Rhino84 — Tue, 27 Jul 2021 22:31:17 GMT

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

Re: Help Needed

Rhino84 — Tue, 27 Jul 2021 22:57:29 GMT

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?

Re: Help Needed

Reeza — Tue, 27 Jul 2021 23:01:50 GMT

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.

Re: Help Needed

Rhino84 — Tue, 27 Jul 2021 23:06:16 GMT

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

Re: Help Needed

jimbarbour — Tue, 27 Jul 2021 23:10:52 GMT

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;

Re: Help Needed

mkeintz — Wed, 28 Jul 2021 14:26:49 GMT

Since:

You apparently have exactly 1 record with ID=300 or 500 per group
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.

Re: Help Needed

Reeza — Wed, 28 Jul 2021 14:17:26 GMT

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

Re: Help Needed

Rhino84 — Wed, 28 Jul 2021 18:18:35 GMT

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

Re: Help Needed

Rhino84 — Wed, 28 Jul 2021 18:19:25 GMT

This gives me the following o/p as above.

Re: Help Needed

Reeza — Wed, 28 Jul 2021 18:46:13 GMT

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

Re: Help Needed

Rhino84 — Wed, 28 Jul 2021 19:16:15 GMT

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

Re: Help Needed

jimbarbour — Wed, 28 Jul 2021 19:41:26 GMT

Did you try my code? See my first response, above.

Here are the results. Isn't this what you want?

Jim

Re: Help Needed

Rhino84 — Wed, 28 Jul 2021 19:57:38 GMT

This worked Finally. Thank you all.

Re: Help Needed

Reeza — Wed, 28 Jul 2021 20:02:13 GMT

If you ever have multiple rates, ie 300 & 500 in a record this will break though. So verify your data.