Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Find a Community

- Home
- /
- SAS Programming
- /
- SAS Procedures
- /
- sum equal zero for variables for sas

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

06-19-2014 09:38 AM

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.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

06-19-2014 09:56 AM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

06-19-2014 09:59 AM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

06-19-2014 10:20 AM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

06-19-2014 10:13 AM

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.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

06-19-2014 10:19 AM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

06-19-2014 10:21 AM

Andy,

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

var _numeric_;

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

06-20-2014 09:09 AM

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.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

06-19-2014 10:25 AM

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;

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

06-19-2014 10:32 AM

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?

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

06-19-2014 10:51 AM

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.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

06-19-2014 10:53 AM

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 |

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

06-19-2014 10:32 AM

Wow!

There's always something to learn.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

06-19-2014 10:38 AM

Would a proc summary work better?

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

06-19-2014 10:42 AM

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