topic Re: What is the most efficient way to fill in blank cells w/ NA? in SAS Programming

What is the most efficient way to fill in blank cells w/ NA?

Hello_there — Mon, 05 Dec 2022 20:17:33 GMT

Hi, I'm working with a table in which i have many visits, and I'm trying to fill in NA (NA) (for mean (sd)) and NA (for median and n) for cell values that are blank.

What is the most efficient way to do this?

data have;
infile datalines dsd dlm=",";
	input visit $ stat $ trt1 $ trt2 $ trt3 $;
datalines;
001, mean_std, 5 (3), 4 (2), 5 (3)
001, median, 3, 3, 3
001, n, 2, 4, 5
002, mean_std, , , 
002, median, , ,
002, n, , , 
003, mean_std, 2 (4), 4 (3), 6, (1)
003, median, 1, 1, 1
003, n, 2, 2, 9
; 
run;

desired output:

Re: What is the most efficient way to fill in blank cells w/ NA?

PaigeMiller — Mon, 05 Dec 2022 20:22:31 GMT

More context is needed. Why are you doing this? Is it because you want NA in a data set so that you can perform additional programming tasks on it (what programming tasks), or you want NA in a report, or something else?

Is this really the raw data you are working from, or did you run PROC MEANS on some actual data and want this re-arranged as you show?

Context, please!

Re: What is the most efficient way to fill in blank cells w/ NA?

Hello_there — Mon, 05 Dec 2022 20:24:07 GMT

Hi PaigeMiller,
I ran proc means and i would like it rearranged as shown.

After i had transposed it, there were lots of blank cells that need to be filled in.

For future reference i guess it would be best to specify that this is for a table/report instead of needed in a data set.

Re: What is the most efficient way to fill in blank cells w/ NA?

PaigeMiller — Mon, 05 Dec 2022 20:29:42 GMT

Please show us the output from PROC MEANS. Or is that what you are showing us already in your first message?

Re: What is the most efficient way to fill in blank cells w/ NA?

Hello_there — Mon, 05 Dec 2022 20:39:23 GMT

This is my personal computer, so I don't have access to the actual output. I make these mock examples that are representative of the nature of my problem. I make up the data and hardcode it in a data step with the datalines statement.

The issue I'm having is coming up with the most efficient way to fill in the blank cells w/ "NA (NA) "for when stat = "mean_std" and trt1 or trt2 or tr3 is blank
and also fill in "NA" for when stat=median or stat=n and either trt1 or trt2 or trt3 is blank.

I wish i could give you more context, but this is more a data manipulation problem than anything else. Perhaps there's a keyword in PROC Means i don't know that fills 0 for all combinations of treatment and visit, but it appears that proc only counts non missing values in the VAR statement.

Re: What is the most efficient way to fill in blank cells w/ NA?

PaigeMiller — Mon, 05 Dec 2022 20:40:27 GMT

It's just a data manipulation exercise. I got it.

So important data manipulation concept: just because you want NAs in a report doesn't mean you have to have NAs in a data set.

That's so important, I will say it again: just because you want NAs in a report doesn't mean you have to have NAs in a data set.

You can create this table in any of a number of ways (PROC TABULATE, PROC REPORT, maybe even PROC PRINT and the %TABLEN macro), and by setting the proper option, your missings will appear as NA. This can also be done with custom formats. So ... there's no need to create a DATA set with NAs in place of missing values.

options missing='NA';

Re: What is the most efficient way to fill in blank cells w/ NA?

Hello_there — Mon, 05 Dec 2022 20:42:28 GMT

Thanks for the reply and the explanation.

But this is a unique situation where the value can take 1 of 2 possible values for a blank cell, either "NA" for n or median, or "NA (NA)" for mean_std. So this is not quite i was looking for, although this is a useful trick

Re: What is the most efficient way to fill in blank cells w/ NA?

Tom — Mon, 05 Dec 2022 20:45:07 GMT

@PaigeMiller wrote:

It's just a data manipulation exercise. I got it.

So important data manipulation concept: just because you want NAs in a report doesn't mean you have to have NAs in a data set.

That's so important, I will say it again: just because you want NAs in a report doesn't mean you have to have NAs in a data set.

You can create this table in any of a number of ways (PROC TABULATE, PROC REPORT, maybe even PROC PRINT and the %TABLEN macro), and by setting the proper option, your missings will appear as NA. This can also be done with custom formats. So ... there's no need to create a DATA set with NAs in place of missing values.
options missing='NA';

That cannot work. The MISSING option can only accept a single character.

Re: What is the most efficient way to fill in blank cells w/ NA?

PaigeMiller — Mon, 05 Dec 2022 20:45:55 GMT

@Tom wrote:
@PaigeMiller wrote:

It's just a data manipulation exercise. I got it.

So important data manipulation concept: just because you want NAs in a report doesn't mean you have to have NAs in a data set.

That's so important, I will say it again: just because you want NAs in a report doesn't mean you have to have NAs in a data set.

You can create this table in any of a number of ways (PROC TABULATE, PROC REPORT, maybe even PROC PRINT and the %TABLEN macro), and by setting the proper option, your missings will appear as NA. This can also be done with custom formats. So ... there's no need to create a DATA set with NAs in place of missing values.
options missing='NA';
That cannot work. The MISSING option can only accept a single character.

Okay, I stand corrected, but custom formats would work fine here.

Re: What is the most efficient way to fill in blank cells w/ NA?

Tom — Mon, 05 Dec 2022 20:49:18 GMT

Just use an ARRAY.

data have;
  infile datalines dsd ;
	input visit $ stat $ trt1 $ trt2 $ trt3 $;
datalines;
001, mean_std, 5 (3), 4 (2), 5 (3)
001, median, 3, 3, 3
001, n, 2, 4, 5
002, mean_std, , , 
002, median, , ,
002, n, , , 
003, mean_std, 2 (4), 4 (3), 6, (1)
003, median, 1, 1, 1
003, n, 2, 2, 9
; 

data want;
  set have ;
  array trt [3] ;
  do index=1 to dim(trt);
    if missing(trt[index]) then do;
      if stat in ('median' 'n') then trt[index]='NA';
      if stat in ('mean_std') then trt[index]='NA (NA)';
    end;
  end;
run;

proc print;
run;

Obs    visit    stat        trt1       trt2       trt3       index

 1      001     mean_std    5 (3)      4 (2)      5 (3)        4
 2      001     median      3          3          3            4
 3      001     n           2          4          5            4
 4      002     mean_std    NA (NA)    NA (NA)    NA (NA)      4
 5      002     median      NA         NA         NA           4
 6      002     n           NA         NA         NA           4
 7      003     mean_std    2 (4)      4 (3)      6            4
 8      003     median      1          1          1            4
 9      003     n           2          2          9            4

Re: What is the most efficient way to fill in blank cells w/ NA?

Hello_there — Mon, 05 Dec 2022 20:52:20 GMT

Thanks, Tom! This is perfect.

Re: What is the most efficient way to fill in blank cells w/ NA?

Tom — Mon, 05 Dec 2022 20:54:20 GMT

You should have handled the missing values before you transposed it.

Re: What is the most efficient way to fill in blank cells w/ NA?

ballardw — Mon, 05 Dec 2022 20:54:48 GMT

Proc tabulate table option MISSTEXT does work though. The Sashelp.class data set has no females age 16 so when you run

proc tabulate data=sashelp.class;
   class sex age;
   var height;
   table age * height*(max min),
         sex
         /misstext='NA'
   ;
run;

The result is

			Sex
			F	M
Age			51.30	57.50
11	Height	Max	51.30	57.50
11	Height	Min	51.30	57.50
12	Height	Max	59.80	64.80
12	Height	Min	56.30	57.30
13	Height	Max	65.30	62.50
13	Height	Min	56.50	62.50
14	Height	Max	64.30	69.00
14	Height	Min	62.80	63.50
15	Height	Max	66.50	67.00
15	Height	Min	62.50	66.50
16	Height	Max	NA	72.00
16	Height	Min	NA	72.00

Re: What is the most efficient way to fill in blank cells w/ NA?

Hello_there — Mon, 05 Dec 2022 21:02:51 GMT

My actual data came from PROC means and when I transposed it by visit, with id being treatment, that’s when the blanks appeared bc not every treatment had a visit

Re: What is the most efficient way to fill in blank cells w/ NA?

Tom — Mon, 05 Dec 2022 21:04:32 GMT

@Hello_there wrote:
My actual data came from PROC means and when I transposed it by visit, with id being treatment, that’s when the blanks appeared bc not every treatment had a visit

Show the code. I have never seen PROC MEANS make some thing that looks like '3 (4)' as output. I generally only creates numeric values.

Re: What is the most efficient way to fill in blank cells w/ NA?

Hello_there — Mon, 05 Dec 2022 21:11:35 GMT

That was an extra step I took to get it into the format I needed in an intermediary data step. This is my personal computer so I don’t have access to the actual output.

But when you use PROC means to get summary statistics by visit for each treatment, it only summarizes the actual data in the raw data set. When you transpose it, there will be missing values for treatments that didn’t have a visit.
For example, subjects in treatment one can have data for visit 001, but if treatment 2 doesn’t have data for visit 001, then it will be blank.

My example wasn’t really accurate in the OP bc one of the treatments has to have data for a visit to populate in the data step. If there is no data for all treatments, there are no visits.

Re: What is the most efficient way to fill in blank cells w/ NA?

Tom — Mon, 05 Dec 2022 21:21:21 GMT

So do the step to add the visits before the proc means. Then the step that is making it pretty for the transpose can deal with the missing values.

If you don't have a dataset with the set of valid id*visit combination you can easily make one.

Example:

data have;
  input id visit value ;
cards ;
1 1 100
1 2 112
2 1 200
3 2 300
;
proc sql ;
create table for_analysis as 
select * 
from have 
natural full join 
  ( select * from (select distinct id from have),(select distinct visit from have) )
;
quit;

Re: What is the most efficient way to fill in blank cells w/ NA?

Hello_there — Mon, 05 Dec 2022 21:26:49 GMT

This works also!

Re: What is the most efficient way to fill in blank cells w/ NA?

Hello_there — Mon, 05 Dec 2022 21:29:31 GMT

The array would be more efficient though right? Because if you did the extra step, you would still have to address the missing values at the end. But maybe this way is less lines of code.

Re: What is the most efficient way to fill in blank cells w/ NA?

Hello_there — Mon, 05 Dec 2022 22:31:41 GMT

Do you know if it's possible to output this into a data set? I try use "ods output out=xyz" in a separate line of code, but doesn't work.