topic Re: Proc tabulate with multiple where conditions working with large data in New SAS User

Proc tabulate with multiple where conditions working with large data

bobi — Thu, 12 Mar 2020 16:18:24 GMT

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

Re: Proc tabulate with multiple where conditions working with large data

Reeza — Thu, 12 Mar 2020 16:27:24 GMT

What do you mean PROC MEANS will give you 60+ tables? Why not pipe your PROC MEANS to a data set and store your results for display later. I would be turning off any output and coding my rules into the system rather than manually verifying your data by eyeballing it.

Re: Proc tabulate with multiple where conditions working with large data

bobi — Thu, 12 Mar 2020 17:15:11 GMT

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

Re: Proc tabulate with multiple where conditions working with large data

ballardw — Thu, 12 Mar 2020 23:48:11 GMT

@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

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

Re: Proc tabulate with multiple where conditions working with large data

Patrick — Fri, 13 Mar 2020 03:51:10 GMT

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.