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.
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.
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.;
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 ...
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.
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
Andy,
Sorry, I thought the default would be to use all numeric variables. Try adding to the proc:
var _numeric_;
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.
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;
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?
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.
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 |
Wow!
There's always something to learn.
Would a proc summary work better?
I'm going to tears ... ok , almost.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.