I cannot describe how idiotic I feel not being able to do this. I have two columns (SELECT_FIRST and ATTR_FIRST) they can both be filled, both be empty, or one empty and the other not. I want to add two new columns (denoted with _V2 ). I want this code to say if both are empty then make the two new columns say 'NA'. If SELECT_FIRST is empty but ATTR_FIRST is not, then add Make both columns the value is ATTR_FIRST. Likewise if ATTR_FIRST is empty then make both columns the value of SELECT_FIRST. If both are filled then make each new column equal to their respective column. Any and all help would be much appreciated!!
DATA TMP7DAY.ATT_EXHIBIT_V2;
SET TMP7DAY.ATT_EXHIBIT;
IF missing(SELECT_FIRST) AND missing(ATTR_FIRST) THEN SELECT_FIRST_V2 = 'NA' AND ATTR_FIRST_V2 = 'NA';
ELSE IF missing(SLCT_ID) THEN SELECT_FIRST_V2 = ATTR_FIRST;
ELSE IF missing(ATRBT_ID) THEN ATTR_FIRST_V2 = SELECT_FIRST;
ELSE SELECT_FIRST_V2 = SELECT_FIRST AND ATTR_FIRST_V2 = ATTR_FIRST;
RUN;
There you go. Seems there was an error in my previous code
data have;
input SELECT_FIRST $ ATTR_FIRST $;
infile datalines dlm=',' dsd;
datalines;
Bill,
Gary,Robert
Thomas,
,
Thomas,Thomas
Simon,
,
Simon,Bill
;
data want;
set have;
length select_first_v2 $50 attr_first_v2 $50;
if SELECT_FIRST=' ' and ATTR_FIRST=' ' then do;
select_first_v2 = 'NA';
attr_first_v2 = 'NA';
end;
else if SELECT_FIRST=' ' and ATTR_FIRST ne ' ' then do;
select_first_v2 = ATTR_FIRST;
attr_first_v2 = ATTR_FIRST;
end;
else if SELECT_FIRST ne ' ' and ATTR_FIRST=' ' then do;
select_first_v2 = SELECT_FIRST;
attr_first_v2 = SELECT_FIRST;
end;
else do;
select_first_v2 = SELECT_FIRST;
attr_first_v2 = ATTR_FIRST;
end;
run;
Modifying your own code..
data have;
input SELECT_FIRST $ ATTR_FIRST $;
datalines;
. 1
2 .
. .
3 4
;
data want;
set have;
if SELECT_FIRST=' ' and ATTR_FIRST=' ' then do;
select_first_v2 = 'NA';
attr_first_v2 = 'NA';
end;
else if SELECT_FIRST=' ' and ATTR_FIRST ne . then do;
select_first_v2 = ATTR_FIRST;
attr_first_v2 = ATTR_FIRST;
end;
else if SELECT_FIRST ne ' ' and ATTR_FIRST=. then do;
select_first_v2 = SELECT_FIRST;
attr_first_v2 = SELECT_FIRST;
end;
else do;
select_first_v2 = SELECT_FIRST;
attr_first_v2 = ATTR_FIRST;
end;
run;
Don't know where your reply went, but you can set an appropriate length for you created v2 variables like this
length select_first_v2 $50 attr_first_v2 $50;
I don't knew where it went either. But I ran the code again and saw that it actually doesn't work. Values in the new columns are still empty when they shouldn't be. Also how would you set length when the strings can be any length?
Ok. Please post some example of what your data looks like. Makes it much easier to provide a usable code answer.
SELECT_FIRST | ATTR_FIRST
Bill
Gary Robert
Thomas
Thomas Thomas
Simon
Simon Bill
There you go. Seems there was an error in my previous code
data have;
input SELECT_FIRST $ ATTR_FIRST $;
infile datalines dlm=',' dsd;
datalines;
Bill,
Gary,Robert
Thomas,
,
Thomas,Thomas
Simon,
,
Simon,Bill
;
data want;
set have;
length select_first_v2 $50 attr_first_v2 $50;
if SELECT_FIRST=' ' and ATTR_FIRST=' ' then do;
select_first_v2 = 'NA';
attr_first_v2 = 'NA';
end;
else if SELECT_FIRST=' ' and ATTR_FIRST ne ' ' then do;
select_first_v2 = ATTR_FIRST;
attr_first_v2 = ATTR_FIRST;
end;
else if SELECT_FIRST ne ' ' and ATTR_FIRST=' ' then do;
select_first_v2 = SELECT_FIRST;
attr_first_v2 = SELECT_FIRST;
end;
else do;
select_first_v2 = SELECT_FIRST;
attr_first_v2 = ATTR_FIRST;
end;
run;
Anytime, glad to help 🙂
Do the variables ATTR_FIRST and SELECT_FIRST contain boolean true/false values like the values you are assigning to SELECT_FIRST_V2 to in the first THEN clause and the terminal ELSE clause?
@mhoward2 wrote:
The values are names. So one row as the two values in the original columns Bill and Robert then I want there new columns to be Bill and Robert. But if its just Bill and 'Empty' then I want both new columns to be Bill.
So it doesn't sound like you need to use IF/THEN. Just use COALESCEC() function.
new_first = coalescec(first,second);
new_second = coalescec(second,first);
Also it is not clear why you want to put 'NA' instead of leaving the values empty, this is SAS and not R. But if you do then just include that as a third argument to the COALESCEC() function calls.
new_first = coalescec(first,second,'NA');
new_second = coalescec(second,first,'NA');
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.