BookmarkSubscribeRSS Feed
andy_wk
Calcite | Level 5

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.

25 REPLIES 25
LinusH
Tourmaline | Level 20

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
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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

andy_wk
Calcite | Level 5

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

Astounding
PROC Star

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.

andy_wk
Calcite | Level 5

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

Astounding
PROC Star

Andy,

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

var _numeric_;


data_null__
Jade | Level 19

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.

Reeza
Super User

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;

andy_wk
Calcite | Level 5

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?

ballardw
Super User

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.

Reeza
Super User

Definitely a character variable:

1proc means data=sashelp.class stackods sum;

2

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

4

5var age weight height name;

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

6

7run;

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
Astounding
PROC Star

Wow!

There's always something to learn.

andy_wk
Calcite | Level 5

Would a proc summary work better?

andy_wk
Calcite | Level 5

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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