I have below piece of data and I want output in such a way that if a person has multiple rows of data then first blank value for cond_cd is removed from the output but if we have single row of data of person then blank value of cond_cd should remain.
data one;
input @001 FirstName $10.
@011 LastName $10.
@021 cond_cd $6.
;
datalines;
JOHN MCCARTHY
JOHN MCCARTHY TGCHS
JOHN MCCARTHY abcde
Akshay KUMAR
Akshay KUMAR TGCHS
SMITA PAREKH
;
run;
Desired output =>
FirstName LastName Cond_cd
JOHN MCCARTHY TGCHS
JOHN MCCARTHY abcde
Akshay KUMAR TGCHS
SMITA PAREKH
exclude the one's with blanks
What if you had several blank values for some name?
keep all of them
So if this was in your data
JOHN MCCARTHY
JOHN MCCARTHY
JOHN MCCARTHY TGCHS
JOHN MCCARTHY abcde
you would want to keep all 4 obs?
exclude the one's with blanks
One way. Provided that your data is gruoped by Names
data one;
input FirstName $ 1-6 LastName $ 8-15 cond_cd $ 17-21;
datalines;
JOHN MCCARTHY
JOHN MCCARTHY TGCHS
JOHN MCCARTHY abcde
Akshay KUMAR
Akshay KUMAR TGCHS
SMITA PAREKH
;
data want;
do _N_=1 by 1 until (last.LastName);
set one;
by LastName notsorted;
end;
do until (last.LastName);
set one;
by LastName notsorted;
if _N_=1 | (not missing(cond_cd) & _N_>1) then output;
end;
run;
Result:
FirstName LastName cond_cd JOHN MCCARTHY TGCHS JOHN MCCARTHY abcde Akshay KUMAR TGCHS SMITA PAREKH
data one;
infile cards truncover;
input FirstName :$10.
LastName : $10.
cond_cd : $6.
;
datalines;
JOHN MCCARTHY
JOHN MCCARTHY TGCHS
JOHN MCCARTHY abcde
Akshay KUMAR
Akshay KUMAR TGCHS
SMITA PAREKH
;
run;
proc sql;
create table want as
select *
from one
group by FirstName,LastName
having count(*)>1 and cond_cd ne ' ' or count(*)=1;
quit;
data one;
infile cards truncover;
input FirstName :$10.
LastName : $10.
cond_cd : $6.
;
datalines;
JOHN MCCARTHY
JOHN MCCARTHY TGCHS
JOHN MCCARTHY abcde
Akshay KUMAR
Akshay KUMAR TGCHS
SMITA PAREKH
;
run;
proc sort data=one;
by FirstName LastName;
run;
data want;
set one;
by FirstName LastName;
if not (first.LastName and last.LastName) and missing(cond_cd) then delete;
run;
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.