topic Re: Add additional variables and assign the values from another rows in SAS Programming

Add additional variables and assign the values from another rows

pangea17 — Thu, 14 Feb 2019 00:45:45 GMT

I have 100 rows of data and I have an account#, a segment, and a score for each row. I want to create another field(column) for each row that is the highest score for that segment. I figured I would sort everything by segment and then descending score first. But not sure what the next step would be maybe a data step if first.segment and first.score then newvariable = score. ??

Acct#	Segment	Score	New Field
123	A	2	14
124	A	9	14
125	A	14	14
126	A	10	14
127	A	12	14
128	A	3	14
129	A	7	14
130	A	5	14
230	A	8	71
231	B	15	71
232	B	7	71
233	B	22	71
234	B	15	71
235	B	71	71
236	B	45	71
237	B	62	71
238	B	29	71
239	B	30	71

Re: Add additional variables and assign the values from another rows

pangea17 — Thu, 14 Feb 2019 01:21:39 GMT

I don't know if this is the best way to accomplish this task but I was able to do the following.

proc means data = table1 max nonobs noprint;

class segment;

ways 1;

var CEV;

output out= table2 (drop= _freq_ _type_) max=TopScore;

RUN;

proc sql;

create table2 AS

select a.*, b.TopScore

from table1 a left join table2 b ON a.segment=b.segment

QUIT;

Re: Add additional variables and assign the values from another rows

PeterClemmensen — Thu, 14 Feb 2019 01:45:20 GMT

data have;
input Acct	Segment $	Score;
datalines;
123	A	2 
124	A	9 
125	A	14
126	A	10
127	A	12
128	A	3 
129	A	7 
130	A	5	
230	A	8	
231	B	15	
232	B	7
233	B	22
234	B	15
235	B	71
236	B	45
237	B	62
238	B	29
239	B	30
;

proc sql;
   create table want as
   select *,
          max(Score) as NewField
   from have
   group by Segment;
quit;

Re: Add additional variables and assign the values from another rows

r_behata — Thu, 14 Feb 2019 01:47:31 GMT

data have;
	input Acct $ 	Segment $	Score;
	cards;
123 A 2
124 A 9
125 A 14
126 A 10
127 A 12
128 A 3
129 A 7
130 A 5
230 A 8
231 B 15
232 B 7
233 B 22
234 B 15
235 B 71
236 B 45
237 B 62
238 B 29
239 B 30
;
run;

Proc sql;
	create table want as select acct, segment, score, (select max(score) from have a 
		where a.segment=b.segment group by segment) as new_field from have b;
quit;

Re: Add additional variables and assign the values from another rows

r_behata — Thu, 14 Feb 2019 02:06:04 GMT

proc sort data=have;
	by segment score;
run;

data want;
	do until(last.segment);
		set have;
		by segment score;
		new_field=score;
	end;

	do until(last.segment);
		set have;
		by segment score;
		output;
	end;
run;

Re: Add additional variables and assign the values from another rows

PeterClemmensen — Thu, 14 Feb 2019 02:06:25 GMT

data want;
   do until (last.segment);
      set have;
      by Segment;
      NewField=max(NewField, Score);
   end;

   do until (last.segment);
      set have;
      by Segment;
      output;
   end;
run;