topic Re: Proc Tabulate - Some records were not included because of missing values in SAS Programming

Proc Tabulate - Some records were not included because of missing values

Mick_bill — Mon, 06 Feb 2023 20:23:28 GMT

Hello,

I am trying to code get a tabulate to work to output all my different outcomes in one table, but i am having issues. I am getting the error

Some records were not included because of missing values

I am not sure why this is or how to get round it.

I have code up variables as a flag = 1 if it meets the criteria.

E.g.

If X > 1 then Var1 = 1;

If Y > 250 then Var 2 = 1;

Count = 1;

etc.

I am then trying to split these by a flag. I have the data create a mock data shown below:

Flag	Var1	Var2	Var3	Var4	Weight
Yes	.	1	.	1	2
No	.	1	.	1	2.5
Maybe	1	.	.	1	1
Potential	1	.	1	.	1
Yes	1	.	.	1	2

From that data I am then trying to code it to look like this

Then i produce the code

PROC TABULATE DATA = DATA;
TABLES Var1 Var2 Var3 Var4, Flag*(COUNT);
CLASS E1_A_01_HIT E1_A_02_HIT E1_A_03_HIT
GBF;
VAR COUNT / WEIGHT = Weight;
RUN;

I am after the table so i can get it all in one table. All the Flags Yes, No maybe etc would be populated with a number but for this example you can see what I am aiming for. Do you know how to populated this from the table and why the error is being created or another way of getting the table i am after.

Thank you,

	Yes	No	Maybe	Potential
Var1	2		1	1
Var2	2	2.5
Var3				1
Var4	4	1.5	1

PLEASE FIND THE CODE.

data ORIGINAL;

input VAR1 VAR2 VAR3 VAR4 VAR5 WEIGHT FLAG $;

datalines;

1 1 . . . 2.5 Z

1 1 1 1 1 4 Y

1 1 . . . 2.5 X

1 1 . . 1 1 Z

1 . 1 1 . 1 Z

1 . 1 1 . 1 Y

1 . . . . 3 X

;
RUN;
DATA ORIGINAL; SET ORIGINAL;
COUNT =1;
RUN;

PROC TABULATE DATA = ORIGINAL;
TABLES VAR1 VAR2 VAR3 VAR4 VAR5, FLAG*(COUNT);
CLASS VAR1 VAR2 VAR3 VAR4 VAR5
FLAG;
VAR COUNT / WEIGHT = WEIGHT;
RUN;

THE LOG

10547 PROC TABULATE DATA = ORIGINAL;
10548 TABLES VAR1 VAR2 VAR3 VAR4 VAR5, FLAG*(COUNT);
10549 CLASS VAR1 VAR2 VAR3 VAR4 VAR5
10550 FLAG;
10551 VAR COUNT / WEIGHT = WEIGHT;
10552 RUN;
NOTE: Some records were not included because of missing values
NOTE: 7 observations were read from "WORK.ORIGINAL"
NOTE: Procedure TABULATE step took :
real time : 0.006
cpu time : 0.015

10553 quit; run;

PROC FREQ DATA = ORIGINAL; TABLE (VAR1 VAR2 VAR3 VAR4 VAR5)*FLAG / NOROW NOCOL NOPERCENT; WEIGHT WEIGHT;
RUN;

the Proc Freq outputs what i would like to see but not in the single table i would of would much prefer.

Thanks,

Re: Proc Tabulate - Some records were not included because of missing values

PaigeMiller — Mon, 06 Feb 2023 20:18:13 GMT

You are getting an error? First thing for you to do is to LOOK AT the data set with your own eyes to see if missing values are present which could be a problem. If that doesn't help ...

Show us the LOG from your code. We need to see the ENTIRE log. Please copy the log as text and paste it into the window that appears when you click on the </> icon. Please follows these instructions carefully.

We also need a portion of the actual data, presented as SAS data step code. You can type it in yourself as Cynthia did in your earlier thread, or follow these instructions.

Re: Proc Tabulate - Some records were not included because of missing values

Mick_bill — Mon, 06 Feb 2023 20:23:59 GMT

Sorry, this has been added.

Re: Proc Tabulate - Some records were not included because of missing values

Astounding — Mon, 06 Feb 2023 20:31:29 GMT

It's less than crystal clear where you are headed, but here are some general ideas.

It may be a good idea to set your new variables to 1 or 0, rather than 1 or missing.

And PROC TABULATE automatically excludes any observation that has a missing value for any CLASS variable. You can override that by adding the word MISSING (I believe it goes on the PROC statement).

Re: Proc Tabulate - Some records were not included because of missing values

ballardw — Tue, 07 Feb 2023 06:13:54 GMT

I agree that it is incomprehensible what you actually have or want to display.

You show code:

If X > 1 then Var1 = 1;
If Y > 250 then Var 2 = 1;

but your data set Original has no variable X or Y. You aren't particularly clear about what should happen with X is <= to 1, or Y<=250.

You have tabulate code using variables E1_A_01_HIT E1_A_02_HIT E1_A_03_HIT which are also not in "Original" data. Which do not have any obvious correlation with the data that you do show. And where are you setting the "flag" variable get Yes No Maybe Potential? Your "data" has 3 values for the Flag but you show 5 rows of output. Plus tabulate would group the first Yes with the second Yes values.