Creating new columns

[sasuser_sk]

Hello Everyone:

I have ID,Group A and Group B in data and want to create two new columns : first(create col1) based on ID and Group A and then second(create col2) based on ID and Group B. Below given table will help to get an idea on what I'm trying to do.

Any help would be appreciated. Thanks much!

ID	GROUP A	GROUP B	CREATE COL1	CREATE COL2
12	A		A
13	DD		DD
14	SD		SD
15	DS		DS
16		PLUS	FG	PLUS
16	FG		FG	PLUS
17	GF		GF
18
19		GREAT	HNGF	GREAT
19	HNGF		HNGF	GREAT
20		EXTREME	JKH	EXTREME
20	JKH		JKH	EXTREME
21	KL
22
23

[sasuser_sk]

Hello Everyone:

I have ID,Group A and Group B in data and want to create two new columns : first(create col1) based on ID and Group A and then second(create col2) based on ID and Group B. Below given pic will help to get an idea on what I'm trying to do.

Any help would be appreciated. Thanks much!

[ballardw]

---

@sasuser_sk wrote:

Hello Everyone:

I have ID,Group A and Group B in data and want to create two new columns : first(create col1) based on ID and Group A and then second(create col2) based on ID and Group B. Below given pic will help to get an idea on what I'm trying to do.

Any help would be appreciated. Thanks much!

---

You did not describe any rules for what creates which value. A solution could be done for exactly that data but not anything else.

[sasuser_sk]

ID has some duplicates. I'm trying to create col1 to fill in the null in group A based on the duplicate records from ID. Similar concept for col2 but based on group B. I hope this helps.

[sasuser_sk]

ID has some duplicates. I'm trying to create col1 to fill in the null in group A based on the duplicate records from ID. Similar concept for col2 but based on group B. I hope this helps better.

[Reeza · Accepted solution]

Please do not post the same question multiple times, and preferably with the data as text not as an image (thank you for including that in your second post).

SQL summary statistics work on text data so taking the max/min will fill the value in for every row with the same ID. 

A data step method would be to remove duplicates and missing from the data set and then merge the original data set with the filtered data set. 

proc sql;
create table want as
select *, max(groupA) as newCol1, max(groupB) as newCol2
from have
group by ID
order by ID;
quit;

---

@sasuser_sk wrote:

Hello Everyone:

I have ID,Group A and Group B in data and want to create two new columns : first(create col1) based on ID and Group A and then second(create col2) based on ID and Group B. Below given table will help to get an idea on what I'm trying to do.

Any help would be appreciated. Thanks much!

ID	GROUP A	GROUP B	CREATE COL1	CREATE COL2
12	A		A
13	DD		DD
14	SD		SD
15	DS		DS
16		PLUS	FG	PLUS
16	FG		FG	PLUS
17	GF		GF
18
19		GREAT	HNGF	GREAT
19	HNGF		HNGF	GREAT
20		EXTREME	JKH	EXTREME
20	JKH		JKH	EXTREME
21	KL
22
23

---

[sasuser_sk]

Hi Reeza-Thank you so much. I have not used SAS community much in the past and therefore don't know which group would respond quicker. I truly appreciate your help here and found the solution with your help.

[novinosrin]

Hi @sasuser_sk  See if the following helps? I believe it should. If it does, you could thank @mkeintz  directly. To whom I owe a lot of my learning plus this one.

data have;
infile cards truncover;
input ID (GROUP_A	GROUP_B) ($);
cards;
12	A	.
13	DD	.
14	SD	.
15	DS	.
16	.	PLUS
16	FG	.
17	GF	.
18	.	.
19	.	GREAT
19	HNGF	
20	.	EXTREME
20	JKH	.
21	KL	.
22	.	.
23	.	.
;

data want;
 if 0 then set have;
 do until(last.id);
  update have(obs=0 rename=(GROUP_A=a GROUP_b=b)) have(rename=(GROUP_A=a GROUP_b=b));
  by id;
 end;
 do until(last.id);
  set have;
  by id;
  output;
 end;
run;

[sasuser_sk]

Hi Novinosrin - Thanks a lot! your script worked perfect as well. Thank you @mkeintz. 

I'm in earlier stages of learning and would greatly appreciate any help in future also.

[sasuser_sk]

I wish I could accept both responses as my solution. But thanks again @novinosrin.