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 |
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
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!
@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.
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.
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.
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
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.
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;
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.
I wish I could accept both responses as my solution. But thanks again @novinosrin.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.