topic Re: Call missing not working in SAS Programming

Call missing not working

Mscarboncopy — Thu, 03 Jun 2021 15:03:30 GMT

I am using a double transpose (main data file A) to create two date variables. However, I have a lot of missing dates, as they are only given if the actual variable that is linked to the dates was entered. Main data file was sorted ahead of time using the same variables in the by statements below. The two dates are not correlated.

Here is my code. What am I doing wrong that the call missing is not working? Thank you.

proc transpose data=A
out=out1;
var pdte1 - pdte53;
by ID a b c;
proc transpose data=A
out=out2;
var date1 - date73;
by ID a b c ;
Run;

Data want;
merge out1(Rename = (COL1 = Pdate)) ** how do I keep only the non missing dates?
out2 (Rename = (COL1 = Ddate)) ; ** how do I keep only the non missing dates?
*by ID a b c ;

output;
call missing(of _All_); * this is not working and it is a problem;
Run;

What I am getting (each ID ends up with 73 entries):

ID Pdate Ddate

1 . .

1 1/1/2001 .

1 . 2/2/2002

2 10/11/2011 9/11/2005

2 . .

3 . .

3 5/5/2003 6/6/2009

4 . .

5 . 5/7/2006

5 . .

5 6/62003 .

I needed the file to look like this:

ID Pdate Ddate

1 1/1/2001 2/2/2002

2 10/11/2011 9/11/2005

3 5/5/2003 6/6/2009

5 6/62003 5/7/2006

Re: Call missing not working

ballardw — Thu, 03 Jun 2021 15:10:03 GMT

Call missing is working just fine.

Your code:

Data want;
merge out1(Rename = (COL1 = Pdate)) ** how do I keep only the non missing dates?
out2 (Rename = (COL1 = Ddate)) ; ** how do I keep only the non missing dates?
*by ID a b c ;

output;
call missing(of _All_);  * this is not working and it is a problem;
Run;

is executing Call MIssing after the data is written to the data set. The OUTPUT statement is when the data is written.

You can use a WHERE= data set option to keep records.

out1(Rename = (COL1 = Pdate)  where=(not missing(Pdate)) )

Assuming the variable you mean to filter on is the Pdate. We don't know if you might have other "date" varaible(s) involved.

You might look into the PREFIX option in Proc transpose to avoid having to completely rename your variable.

Re: Call missing not working

Tom — Thu, 03 Jun 2021 15:18:59 GMT

I would get rid of them while PROC TRANSPOSE is writing its output.

To keep the by groups with neither DATE or PDATE values remerge with the original input.

proc transpose data=A prefix=PDATE out=out1(where=(not missing(pdate1));
  by ID a b c;
  var pdte1 - pdte53;  
run;
proc transpose data=A prefix=DATE out=out2(where=(not missing(date1));
  by ID a b c;
  var date1 - date73;  
run;

data want  ;
  merge a (keep= ID a b c) out1 out2;
  by  ID a b c;
  drop _name_;
run;

What output do you want when there are multiple non-missing PDATE values and/or multiple non-missing DATE values?

Re: Call missing not working

Mscarboncopy — Thu, 03 Jun 2021 15:21:11 GMT

This works perfectly. Thank you.

Re: Call missing not working

Mscarboncopy — Thu, 03 Jun 2021 15:27:36 GMT

That is a good point. I would have missing values since it is pairing pdate with date - and as you can see I have more (potential) dates (73) than pdates (53) However, as long as I am not losing data it should not be an issue. I will do a spot check to see if that is the case. Thank you.