topic Re: SAS 9.3 proc means problem in SAS Procedures

SAS 9.3 proc means problem

xinjian — Mon, 09 Jun 2014 19:16:54 GMT

roblem problemproc
I have a variable with 3 classes and n = 75. Proc means and proc freq all count n=85. What is the problem.

Thanks

Re: SAS 9.3 proc means problem

ballardw — Mon, 09 Jun 2014 19:22:35 GMT

Are you using weights or a freq variable?

Examples of code used might allow us more insight into possible issues.

Re: SAS 9.3 proc means problem

xinjian — Mon, 09 Jun 2014 19:51:50 GMT

it is a simple categorical variable, n=75, 3 classes. Results
showed Frequency missing =10.

The FREQ Procedure
flowblr1
flowblr1	Frequency	Percent	Cumulative	Cumulative
flowblr1	Frequency	Percent	Frequency	Percent
borderline	15	20	15	20
low	9	12	24	32
normal	51	68	75	100
Frequency Missing = 10

Re: SAS 9.3 proc means problem

Reeza — Mon, 09 Jun 2014 20:04:21 GMT

Can you show the code?

If you imported this from an Excel file, you may have accidentally imported some empty rows.

Run a proc contents on the dataset to see what shows up.

proc contents data=have;

run;

Re: SAS 9.3 proc means problem

xinjian — Mon, 09 Jun 2014 20:12:39 GMT

It is an excel file. Here is the code to access data sheet:

PROCIMPORT OUT=
WORK.BASELINE

DATAFILE="f:\V.xls"

DBMS=EXCEL REPLACE

SHEET="Sheet11$";

GETNAMES=YES;

MIXED=NO;

SCANTEXT=YES;

SEDATE=YES;

SCANTIME=YES;

RUN;

Re: SAS 9.3 proc means problem

Reeza — Mon, 09 Jun 2014 20:16:34 GMT

Did you run the proc contents? Have you opened the data set to verify how many observations were imported?

Re: SAS 9.3 proc means problem

xinjian — Mon, 09 Jun 2014 20:31:17 GMT

data set showed 75 subjects. it is really strange.

thanks

Re: SAS 9.3 proc means problem

Reeza — Mon, 09 Jun 2014 20:43:58 GMT

Show the proc contents output?

Re: SAS 9.3 proc means problem

stat_sas — Mon, 09 Jun 2014 21:13:01 GMT

Try this and you will get the correct numbers. In excel file you deleted some of the data points (10) and these were imported as part of sas dataset.

proc freq data=have (where=(flowblr1 ne ' '));

table flowblr1;

run;

Re: SAS 9.3 proc means problem

xinjian — Tue, 10 Jun 2014 16:02:42 GMT

Thanks so much.

Re: SAS 9.3 proc means problem

Reeza — Tue, 10 Jun 2014 17:10:05 GMT

That's not a good solution. You should make sure your data imports correctly instead and/or clean up the data before processing.

Re: SAS 9.3 proc means problem

stat_sas — Tue, 10 Jun 2014 17:52:09 GMT

Thanks for the input Reeza. That is true, data should be cleaned before processing. Could you please recommend some solution to exclude missing values while importing excel data into SAS or through some other method?

Regards,

Re: SAS 9.3 proc means problem

Reeza — Tue, 10 Jun 2014 18:08:59 GMT

Clean it up manually. The problem with the method below is that you may have valid missing obs, so you need to understand the data.

data clean_input;

set have;

if missing(flowblr1) then delete;

run;

Re: SAS 9.3 proc means problem

stat_sas — Tue, 10 Jun 2014 18:23:41 GMT

Thanks Reeza - This works if we understand our data. But when we load data from other applications we are not sure about data structures and we may get missing values at the end of variable(s) which are hard to identify. So processing data after loading into SAS will make the life easier.

Re: SAS 9.3 proc means problem

SteveDenham — Thu, 12 Jun 2014 15:41:37 GMT

Hey Reeza,

This is a chance to pull out my favorite rant about folks who put data in Excel, and then proceed to format the spreadsheet with blank rows or columns that they then squeeze into obliviion. Nothing like pulling in twice as many rows as there are observations, with a boatload of additional variables named F?? that are all null values. Thanks lab guys who think they are helping.

Steve Denham

Re: SAS 9.3 proc means problem

Reeza — Thu, 12 Jun 2014 17:25:39 GMT

Agreed, hidden columns and rows drive me up the wall.

Re: SAS 9.3 proc means problem

esjackso — Thu, 12 Jun 2014 17:47:05 GMT

I know one way that I have found that works is to use excel libname engine (if you have it available). I can run the libname, see how sas is seeing the excel file, then use a data step to bring the file into sas with any cleaning done in the data step. In instances where missing is valid in all fields, I have wrote an array to check across all fields before deleting -- obviously a much bigger pain the larger the dataset.

libname in excel "\excelfile.xlsx";

data out;

drop f_20 - f_25; *blank column / field clean up;

set in."Sheet1"n;

if id = '' then delete; *row clean up -- works as long as id always suppose to have a value;

run;

libname in clear;

Just an added opinion!

Re: SAS 9.3 proc means problem

GreggB — Thu, 12 Jun 2014 17:58:30 GMT

Regarding the topic of cleaning the Excel file, I usually do this (although I admit this is rather inelegant).

Open the Excel file and press Ctrl + End to find the last cell that contains data. Suppose you find that it is in Column X Row 246.

Then, you can write your SHEET option as SHEET='Sheet11$a1:x246';

Re: SAS 9.3 proc means problem

ballardw — Thu, 12 Jun 2014 18:04:23 GMT

Depending on version of Excel the Ctrl-End will find the last row someone manipulated. That row could very well be completely blank. Also you may find yourself in a column way past actual data entered for any record, or at least visible.