Date: Wed, 9 Feb 2011 15:36:33 -0500
Reply-To: "Kirby, Ted" <ted.kirby@LEWIN.COM>
Sender: "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From: "Kirby, Ted" <ted.kirby@LEWIN.COM>
Subject: Re: Filling in missing data with NA
In-Reply-To: <AANLkTin_cmZ3OVx_EEwfY56mO3DPQo7i++3sCLe0imNA@mail.gmail.com>
Content-Type: text/plain; charset="iso-8859-1"
Thank you data_null.
As always, your solution worked perfectly. I will need to read up on PROC SUMMARY to see exactly what I told SAS to do, but the results are exactly what I needed.
-----Original Message-----
From: Data _null_; [mailto:iebupdte@gmail.com]
Sent: Wednesday, February 09, 2011 3:04 PM
To: Kirby, Ted
Cc: SAS-L@listserv.uga.edu
Subject: Re: Filling in missing data with NA
I often use PROC SUMMARY for this type of data expansion.
proc summary data=long nway completetypes;
class hosp meas;
class month / order=data;
output out=long2(drop=_:) idgroup(out(rank)=);
run;
On Wed, Feb 9, 2011 at 1:42 PM, Kirby, Ted <ted.kirby@lewin.com> wrote:
> I have the following code (the "long" dataset is a mocked up version of the real dataset that has 33 hospitals and up to 19 measures for each hospital).
>
> data long;
> input hosp $ meas $ month $ rank $;
> datalines;
> hosp1 15676 Jul10 35
> hosp1 15676 Aug10 34
> hosp1 15676 Sep10 40
> hosp1 15678 Jul10 15
> hosp1 15678 Aug10 13
> hosp1 15678 Sep10 14
> hosp2 15676 Jul10 45
> hosp2 15676 Aug10 40
> hosp2 15676 Sep10 35
> hosp2 15677 Jul10 20
> hosp2 15677 Aug10 25
> hosp2 15677 Sep10 19
> ;
> run;
>
> Notice that hosp1 is missing data for measure 15677 and hosp2 is missing data for measure 15678.
>
> proc transpose data=long out=wide(drop=_NAME_);
> by hosp meas;
> id month;
> var rank;
> run;
>
> proc print; run;
>
> yields what I want (almost):
>
> hosp meas Jul10 Aug10 Sep10
> hosp1 15676 35 34 40
> hosp1 15678 15 13 14
> hosp2 15676 45 40 35
> hosp2 15677 20 25 19
>
> The "almost" part is that I would like the final dataset to be:
>
> hosp meas Jul10 Aug10 Sep10
> hosp1 15676 35 34 40
> hosp1 15677 NA NA NA
> hosp1 15678 15 13 14
> hosp2 15676 45 40 35
> hosp2 15677 20 25 19
> hosp2 15678 NA NA NA
>
>
> Thus, at some point (either before the transposition or after) I need to add data to fill in the value of NA the "rank" field for each month if the hospital did not have data for that measure (and thus would have no rank). (I converted "rank" from a number to a character variable because I want to use the value "NA" if hospitals are missing the data.)
>
> I have tried to figure out how to do this with a macro variable that is a list of the measures or with a hash iterator but could not figure out how to implement logic that says:
>
> if not exist (data for this hospital and this measure) then add (a record for this combination) and set (each month variable equal to "NA").
>
> I hope this request makes sense. If not, please let me know.
>
>
> Ted Kirby
> Consultant,
> The Lewin Group, Inc.
> 3130 Fairview Park Drive, Suite 800
> Falls Church, VA 22042
> Phone: (703)269-5507
> Fax: (703)269-5501
> e-mail: ted.kirby@lewin.com <mailto:ted.kirby@lewin.com>
>
> ************* IMPORTANT - PLEASE READ ********************
>
> This e-mail, including attachments, may include confidential and/or proprietary information,
> and may be used only by the person or entity to which it is addressed. If the reader of this
> e-mail is not the intended recipient or his or her authorized agent, the reader is hereby
> notified that any dissemination, distribution or copying of this e-mail is prohibited. If you
> have received this e-mail in error, please notify the sender by replying to this message
> and delete this e-mail immediately.
>
>
************* IMPORTANT - PLEASE READ ********************
This e-mail, including attachments, may include confidential and/or proprietary information,
and may be used only by the person or entity to which it is addressed. If the reader of this
e-mail is not the intended recipient or his or her authorized agent, the reader is hereby
notified that any dissemination, distribution or copying of this e-mail is prohibited. If you
have received this e-mail in error, please notify the sender by replying to this message
and delete this e-mail immediately.
| 
