topic Re: sum equal zero for variables for sas in SAS Procedures

sum equal zero for variables for sas

andy_wk — Thu, 19 Jun 2014 13:38:31 GMT

I looked at the internet but I could not find anything relevant.

I have a table with thousands of variable.

I'm trying to do a sum of a variable and find out , which variable in sum , is equal to zero.

example

col1 col2 col3

0 0 0

1 0 2

1 0 3

results

col2

0

However, my proc means does not want to take my where clause.

proc sql; create table toto as select nomvar,monotonic() as num_lig from dicofr where nomvar <> 'date';   
proc sql; select nomvar into :varnom separated by ' ' from toto where num_lig between 0 and 1000;  
%put varnom: &varnom;  
proc means data=afr sum (where=(sum(&varnom)=0) ; var &varnom; output out=want; run;

What am I doing wrong?

One thing though, I'm looking for something simple and not overly complex.

TIA for your suggestions.

Re: sum equal zero for variables for sas

LinusH — Thu, 19 Jun 2014 13:56:11 GMT

Do you mean which individual variable(s) that the sum for all rows are 0?

The sum() function adds values on one row only...?

Having data in separate columns is (almost) always as asking for trouble.

Try transpoing your data, and then there will surely be easier ways to query your data.

Re: sum equal zero for variables for sas

RW9 — Thu, 19 Jun 2014 13:59:40 GMT

Added, just seen LinusH's response and complete concur that transposing so that your data looks like:

COL1 0

COL2 0

COL3 0

COL1 1

...

Would be a good idea.

Hi,

Maybe something like:

data have;
col1=0; col2=0; col3=0; output;
col1=1; col2=0; col3=2; output;
col1=1; col2=0; col3=3; output;
run;

proc sql;
select case when sum(COL1)=0 then "COL1"
                when sum(COL2)=0 then "COL2"
                when sum(COL3)=0 then "COL3"
                else "" end
into    :VARNOM
from    HAVE;
quit;

%put &VARNOM.;

Re: sum equal zero for variables for sas

Astounding — Thu, 19 Jun 2014 14:13:10 GMT

It might be difficult for SQL, but it's pretty easy for SAS:

proc summary data=have;

output out=stats sum=;

run;

data names;

length variable_name $ 32;

set stats (drop=_type_ _freq_);

array nums {*} _numeric_;

do _n_=1 to dim(_numeric_);

if nums{_n_}=0 then do;

variable_name = vname(nums{_n_});

output;

end;

keep variable_name;

run;

I'm not sure that qualifies as "simple" but it must be in the ballpark.

Re: sum equal zero for variables for sas

andy_wk — Thu, 19 Jun 2014 14:19:18 GMT

Hi Astounding ,

When running the proc summary, I had the following error

ERROR: Statistics requested in output statement 1, but no analysis variables have been specified. Output statement will

be ignored.

WARNING: No output data set(s) will be created due to error(s) in output statement(s).

ERROR: Neither the PRINT option nor a valid output statement has been given.

NOTE: The SAS System stopped processing this step because of errors.

WARNING: The data set WORK.STATS may be incomplete. When this step was stopped there were 0 observations and 2

variables.

NOTE: PROCEDURE SUMMARY used (Total process time):

real time 0.03 seconds

cpu time 0.03 seconds

Re: sum equal zero for variables for sas

andy_wk — Thu, 19 Jun 2014 14:20:55 GMT

Hi RW9,

The transpose would be a good idea but I have many variables ... close to 6000.

My guess is SQL is hitting a limit when you have so manys ...

Re: sum equal zero for variables for sas

Astounding — Thu, 19 Jun 2014 14:21:51 GMT

Andy,

Sorry, I thought the default would be to use all numeric variables. Try adding to the proc:

var _numeric_;

Re: sum equal zero for variables for sas

Reeza — Thu, 19 Jun 2014 14:25:54 GMT

A slight variation on Astounding - minus the second step by using the stackods option in proc means.

data have;

input col1 col2 col3;

cards;

0 0 0

1 0 2

1 0 3

;

run;

proc means data=have stackods sum;

ods output Summary=WANT (where=(sum=0));

var _numeric_;

run;

Re: sum equal zero for variables for sas

andy_wk — Thu, 19 Jun 2014 14:32:10 GMT

Hi Reeza,

Almost there. I've got a slight issue. My variables have observations with missing values. Once the proc means running, I have the following errors for

some of my variables

ERROR: Variable var47304 in list does not match type prescribed for this list.

ERROR: Variable var110176 in list does not match type prescribed for this list.

ERROR: Variable var152194 in list does not match type prescribed for this list.

47 run;

I thought the sum function would see the missing values as null. it is not the case, why?

Re: sum equal zero for variables for sas

Astounding — Thu, 19 Jun 2014 14:32:49 GMT

Wow!

There's always something to learn.

Re: sum equal zero for variables for sas

andy_wk — Thu, 19 Jun 2014 14:38:44 GMT

Would a proc summary work better?

Re: sum equal zero for variables for sas

andy_wk — Thu, 19 Jun 2014 14:42:28 GMT

I'm going to tears ... ok , almost.

Re: sum equal zero for variables for sas

Reeza — Thu, 19 Jun 2014 14:45:34 GMT

Can you verify that the above variables are numeric not character. Proc Means will just ignore nulls/missing.

Re: sum equal zero for variables for sas

ballardw — Thu, 19 Jun 2014 14:51:54 GMT

Is your data source by any chance a/or created from a DBMS that may have included something like NULL for not assigned values? The import to SAS would possibly make those dolumns character variables. The error is basically you attempted to do something like Sum words, which would be meaningless.

Re: sum equal zero for variables for sas

Reeza — Thu, 19 Jun 2014 14:53:27 GMT

Definitely a character variable:

1	proc means data=sashelp.class stackods sum;

3	ods output Summary=WANT (where=(sum=0));

5	var age weight height name;

ERROR: Variable Name in list does not match type prescribed for this list.

run;

NOTE: The SAS System stopped processing this step because of errors.

NOTE: PROCEDURE MEANS used (Total process time):

	real time	0.36 seconds
	cpu time	0.00 seconds

Re: sum equal zero for variables for sas

jwillis — Thu, 19 Jun 2014 14:56:05 GMT

Andy,

The MONOTONIC() function is not supported in PROC SQL. Using the

MONOTONIC() function in PROC SQL can cause missing or non-sequential

values to be returned.

http://support.sas.com/kb/15/138.html

Re: sum equal zero for variables for sas

andy_wk — Thu, 19 Jun 2014 15:01:28 GMT

Reeza,

Some of the characters are alpha numeric. That is the reason.

Re: sum equal zero for variables for sas

andy_wk — Thu, 19 Jun 2014 15:04:16 GMT

Hi JWillis,

Fair enough. But in my case , it is working quite well as i'm not asking the function to do amazing things ,

just list the number of the observations, which will be like a rowid.

Re: sum equal zero for variables for sas

Reeza — Thu, 19 Jun 2014 15:08:01 GMT

So your actual criteria is sum of numeric variables is 0 and if character variable the number of missing? You can't add character values...

Re: sum equal zero for variables for sas

andy_wk — Thu, 19 Jun 2014 15:13:47 GMT

I'm realizing that , Reeza.

I've tried to do a dos2unix on my csv files to see if it can be fix.