sum equal zero for variables for sas

Frequent Contributor
Posts: 75

sum equal zero for variables for sas

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.

Super User
Posts: 5,884

Re: sum equal zero for variables for sas

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.

Data never sleeps
Super User
Posts: 9,599

Re: sum equal zero for variables for sas

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.;

Frequent Contributor
Posts: 75

Re: sum equal zero for variables for sas

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 ...

Super User
Posts: 6,784

Re: sum equal zero for variables for sas

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;

end;

keep variable_name;

run;

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

Frequent Contributor
Posts: 75

Re: sum equal zero for variables for sas

Posted in reply to Astounding

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

Super User
Posts: 6,784

Re: sum equal zero for variables for sas

Andy,

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

var _numeric_;

Posts: 3,852

Re: sum equal zero for variables for sas

Posted in reply to Astounding

If you change to PROC MEANS, VAR _NUMERIC_; is the default.  Actually it is _NUMERIC_ unless the variable is use somewhere else CLASS or BY for example.

PROC SUMMARY without VAR is "PROC FREQ" sort of.

Super User
Posts: 23,763

Re: sum equal zero for variables for sas

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;

Frequent Contributor
Posts: 75

Re: sum equal zero for variables for sas

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?

Super User
Posts: 13,577

Re: sum equal zero for variables for sas

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.

Super User
Posts: 23,763

Re: sum equal zero for variables for sas

Definitely a character variable:

 1 proc means data=sashelp.class stackods sum;

2

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

4

 5 var age weight height name;

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

6

 7 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
Super User
Posts: 6,784

Re: sum equal zero for variables for sas

Wow!

There's always something to learn.

Frequent Contributor
Posts: 75

Re: sum equal zero for variables for sas

Would a proc summary work better?

Frequent Contributor
Posts: 75

Re: sum equal zero for variables for sas

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

Discussion stats
• 25 replies
• 1729 views
• 3 likes
• 12 in conversation