I am getting last where condition applied to the procedure
I have 400 Million record dataset I dont want to pass it into multiple separate proc tabulates wanted to complete in one proc
I have 3 conditions
variables res brs ats g0s ga
wanted to look min and max for ats to prove it is ats <= 2
wanted to look min and max for res and brs to prove it is 0<=res<=1 or 0<=brs<=1'
wanted to look min and max for g0s and ga to prove it is g0s > 0 or ga > 0'
like wise I have 60 + conditions to prove
I can do it in proc mean to get min and max but if I use preloadfmt it will give me 60+ tables dont want that
data sss;set LIST;run;
proc sort data=sss; by Exclusion_GEN;run;
proc tabulate DATA=sss; missing ;
by Exclusion_GEN;
class Exclusion_GEN ;/*/preloadfmt;*/
VAR res brs ats g0s ga;
where Exclusion_GEN=1;
tables Exclusion_GEN , ats*(N min max) /printmiss misstext='0';
where Exclusion_GEN=2;
tables Exclusion_GEN , brs*(N min max) res *(N min max)/printmiss misstext='0';
where Exclusion_GEN=3;
tables Exclusion_GEN , g0s*(N min max) ga *(N min max)/printmiss misstext='0';
run;
Please let me know
if we direct it to data set we have to code around each and every rule which is more effort to write 60+ conditions and more prone to errors and need more time to code around just wanted to if we can do in tabulate
@bobi wrote:
I am getting last where condition applied to the procedure
I have 400 Million record dataset I dont want to pass it into multiple separate proc tabulates wanted to complete in one proc
I have 3 conditions
variables res brs ats g0s ga
wanted to look min and max for ats to prove it is ats <= 2
wanted to look min and max for res and brs to prove it is 0<=res<=1 or 0<=brs<=1'
wanted to look min and max for g0s and ga to prove it is g0s > 0 or ga > 0'
like wise I have 60 + conditions to prove
I can do it in proc mean to get min and max but if I use preloadfmt it will give me 60+ tables dont want that
data sss;set LIST;run;
proc sort data=sss; by Exclusion_GEN;run;
proc tabulate DATA=sss; missing ;
by Exclusion_GEN;
class Exclusion_GEN ;/*/preloadfmt;*/
VAR res brs ats g0s ga;
where Exclusion_GEN=1;
tables Exclusion_GEN , ats*(N min max) /printmiss misstext='0';
where Exclusion_GEN=2;
tables Exclusion_GEN , brs*(N min max) res *(N min max)/printmiss misstext='0';
where Exclusion_GEN=3;
tables Exclusion_GEN , g0s*(N min max) ga *(N min max)/printmiss misstext='0';
run;
Please let me know
If you want a different set of cross variables for different levels of one variable such as you Exclusion_gen then you are somewhat abusing tabulate as it won't work the way you are attempting. You can get different table calls but not with multiple WHERE statements.
Without some actual data and what the result should look like I'm not going to spend much skull sweat on this. My gut instinct would likely be to use Proc Summary to create the summaries and then possibly proc print with a WHERE based on a _type_ variable from proc summary. Or transpose the data so that instead of res brs ats g0s ga, you have ONE numeric value variable and a CLASS variable that contains the strings "res" "brs" "ats" "g0s" "ga",
If that data is properly structured you would be able to use
proc tabulate DATA=sss; missing ; class Exclusion_GEN namevar ; VAR valuevariable; tables Exclusion_GEN , namevar, valuevariable * (N min max) /printmiss misstext='0'; run;
Which would give a table for each value of Exclusion_GEN and a separate Row for each of the NameVar with the summary of the numeric variable
or
tables Exclusion_GEN , valuevariable, namevar*(N min max) /printmiss misstext='0';
To have a one row summary with the Namevar as column head over the statistics
It would help a lot if you could provide some representative sample mock-up data (via a SAS data step) and then also show us how the desired result using this sample data should look like.
Providing sample data and desired result not only reduces ambiguity, it also allows us to provide tested code as answer.
Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
Register now!
SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.
Find more tutorials on the SAS Users YouTube channel.