topic Re: Creating a new datset with missing values in SAS Programming

Creating a new datset with missing values

kmardinian — Tue, 18 Sep 2018 18:09:35 GMT

Hi, I am working with a dataset that looks like this

Subject Base1 Base2 CHG1 CHG2 VisitID

1 4 5 1

1 4 5 2 3 2

2 4 5 1

3 4 5 1

I'd like to create a new dataset from this data where I can isolate the CHG variables without losing Subject 1 VisitID 1 observation, so that it would look like this

Subject Base1 Base2 CHG1 CHG2 VisitID

1 4 5 1

1 4 5 2 3 2

Any help would be much appreciated. Thank you!

Re: Creating a new datset with missing values

SuryaKiran — Tue, 18 Sep 2018 18:30:43 GMT

What is it your trying to do here?

Re: Creating a new datset with missing values

novinosrin — Tue, 18 Sep 2018 18:35:09 GMT

data have;
infile cards truncover;
input Subject    Base1 Base2 CHG1 CHG2   VisitID;
cards;
1                4           5           . .                      1
1                4           5         2            3         2
2                4           5         . .                         1
3                4           5           .  .                       1
;


proc sql;
create table want as
select *
from have
group by subject
having max((n(CHG1 ,CHG2)>0))=1;
quit;

@kmardinian I think the above will work

Re: Creating a new datset with missing values

kmardinian — Tue, 18 Sep 2018 18:34:24 GMT

I would like to create a dataset with all the subjects that have observations for the CHG variables without losing my VisitID=1 observations.

I've been running this code

data final;
set t4;
if CHG1 ne .;
run;

which obviously doesn't work because then I am losing some of the observations for those subjects, but I'm not sure how else to do it

Re: Creating a new datset with missing values

kmardinian — Tue, 18 Sep 2018 18:39:30 GMT

Thank you! Do you mind explaining what this statement is doing?

max((n(CHG1 ,CHG2)>0))=1;

Re: Creating a new datset with missing values

novinosrin — Tue, 18 Sep 2018 18:41:04 GMT

n function returns the number of non missing values, that's a check and the boolean value is returned is 0 or 1 basing on true of false. And then we filter using having clause to output only true (1) records

Re: Creating a new datset with missing values

kmardinian — Tue, 18 Sep 2018 18:45:42 GMT

I am trying to do it with one CHG variable at a time, but for some reason it is giving me this error 😕

536
537 proc sql;
538 create table final_all as select * from t4
539 group by subnum having max((n(chg1)>0))=1;
ERROR: Summary functions nested in this way are not supported.
540 quit;

Re: Creating a new datset with missing values

Tom — Tue, 18 Sep 2018 18:45:54 GMT

Still not at all clear what you want. Your words say you just want to include records that have visitid=1 or non-missing CHGx values.

    if CHG1 ne . or CHG2 ne . or visitid=1;

But that is not the output that you posted.

To get that you would change the last condition to be just the VISITID=1 to for Subject 1

   if CHG1 ne . or CHG2 ne . or (subject=1 and visitid=1);

Note you didn't specify if VISITID or SUBJECT are numbers or character strings. I have coded as if they are numbers, but in most instances such identifiers would be character strings since you never need to run statistics on them.

Re: Creating a new datset with missing values

novinosrin — Tue, 18 Sep 2018 18:47:34 GMT

Not sure what's your requirement , now you seem to change from your initial. Can you clarify your have and want plz and make it comprehensive for the community

Re: Creating a new datset with missing values

kmardinian — Tue, 18 Sep 2018 18:51:18 GMT

Sorry, it's difficult to properly explain what I was looking for, but Tom's line of code below is exactly what I needed.

Thank you so much for all your help!

   if CHG1 ne . or CHG2 ne . or (subject=1 and visitid=1);

Re: Creating a new datset with missing values

SuryaKiran — Tue, 18 Sep 2018 18:52:44 GMT

Maybe a subquery might help

proc sql;
select * from have
where subject in (select subject from have where chg1 is not null and chg2 is not null);
quit;

Re: Creating a new datset with missing values

kmardinian — Tue, 18 Sep 2018 18:59:32 GMT

Thank you, I will try it this way as well!

Re: Creating a new datset with missing values

FreelanceReinh — Tue, 18 Sep 2018 21:29:57 GMT

@kmardinian wrote:

I am trying to do it with one CHG variable at a time, but for some reason it is giving me this error 😕

536
537 proc sql;
538 create table final_all as select * from t4
539 group by subnum having max((n(chg1)>0))=1;
ERROR: Summary functions nested in this way are not supported.
540 quit;

The reason is: The N function is both a SAS function (to be applied to values within an observation) and an SQL summary function (to be applied to values within a column). As long as there were two arguments it was clear that you meant the SAS function. Now with only one argument it could be the SQL function as well and indeed this is the preferred interpretation by the compiler. Therefore, n(chg1) is already the number of non-missing CHG1 values in the group and the additional SQL summary function MAX is unnecessary and even inappropriate (hence the error).

So, one of several possible corrections is:

having n(chg1);

(Similarly, the original HAVING clause could have been written as having n(CHG1) | n(CHG2).)

Re: Creating a new datset with missing values

Ksharp — Wed, 19 Sep 2018 14:22:54 GMT

data have;
infile cards truncover;
input Subject    Base1 Base2 CHG1 CHG2   VisitID;
cards;
1                4           5           . .                      1
1                4           5         2            3         2
2                4           5         . .                         1
3                4           5           .  .                       1
;

proc sort data=have out=want nodupkey;
by  Base1 Base2 CHG1 CHG2   VisitID;
run;
proc print;run;