topic Re: Excluding missing record where dependent variable has multiple rows of data in SAS Programming

Excluding missing record where dependent variable has multiple rows of data

Abhinav26 — Wed, 30 Oct 2019 11:46:28 GMT

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

Re: Excluding missing record where dependent variable has multiple rows of data

PeterClemmensen — Wed, 30 Oct 2019 11:48:02 GMT

What if you had several blank values for some name?

Re: Excluding missing record where dependent variable has multiple rows of data

Abhinav26 — Wed, 30 Oct 2019 11:48:39 GMT

keep all of them

Re: Excluding missing record where dependent variable has multiple rows of data

PeterClemmensen — Wed, 30 Oct 2019 11:49:47 GMT

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?

Re: Excluding missing record where dependent variable has multiple rows of data

Abhinav26 — Wed, 30 Oct 2019 11:51:04 GMT

exclude the one's with blanks

Re: Excluding missing record where dependent variable has multiple rows of data

PeterClemmensen — Wed, 30 Oct 2019 12:01:56 GMT

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

Re: Excluding missing record where dependent variable has multiple rows of data

novinosrin — Wed, 30 Oct 2019 12:11:29 GMT


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;

Re: Excluding missing record where dependent variable has multiple rows of data

Ksharp — Wed, 30 Oct 2019 12:20:29 GMT


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;