topic Re: Creating new columns in SAS Programming

Creating new columns

sasuser_sk — Tue, 24 Nov 2020 18:08:38 GMT

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

Creating new cols based existing one

sasuser_sk — Tue, 24 Nov 2020 18:03:48 GMT

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!

Re: Creating new cols based existing one

ballardw — Tue, 24 Nov 2020 18:08:11 GMT

@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.

Re: Creating new cols based existing one

sasuser_sk — Tue, 24 Nov 2020 18:11:23 GMT

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.

Re: Creating new columns

sasuser_sk — Tue, 24 Nov 2020 18:13:32 GMT

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.

Re: Creating new columns

Reeza — Tue, 24 Nov 2020 18:24:47 GMT

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

Re: Creating new columns

novinosrin — Tue, 24 Nov 2020 18:37:44 GMT

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;

Re: Creating new columns

sasuser_sk — Tue, 24 Nov 2020 18:54:00 GMT

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.

Re: Creating new columns

sasuser_sk — Tue, 24 Nov 2020 18:56:16 GMT

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.

Re: Creating new columns

sasuser_sk — Tue, 24 Nov 2020 18:58:10 GMT

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