min and max value in once

Accepted Solution Solved
Reply
Contributor
Posts: 60
Accepted Solution

min and max value in once

Hi,

I have a table having millions of record and 100+ variable. I need to find the distinct,min value and max value of each variable. Currently I am running a loop for each variable and accessing the table  every time.Due to which time taken is more for generating the table.

Is there any way to access the table once and get the output in one shot and increase the efficiency of the prog??

Let me know if anyone needs more information.

Please let me know your thoughts.


Accepted Solutions
Solution
‎10-18-2014 04:49 PM
PROC Star
Posts: 7,433

Re: min and max value in once

proc sql noprint;

  select catt('min(',name,') as min_',name,

   ', max(',name,') as max_',name)

    into :select separated by ','

      from dictionary.columns

        where libname='SASHELP' and

              memname='CLASS'

  ;

  create table want as

    select &select.

      from sashelp.class

  ;

quit;

View solution in original post


All Replies
Trusted Advisor
Posts: 1,228

Re: min and max value in once

Hi,

What is distinct min value/max value? There should be only one min or max value. What code are you using for this?

Contributor
Posts: 60

Re: min and max value in once

Hi,

For data analysis purpose I need to gather all the information.My data is moving from one source to another and I need to check the data integrity.

Getting distinct value will help me to check all the value is moving from one source to another or not.Min and Max also help me in same way.

PROC Star
Posts: 7,433

Re: min and max value in once

I think you'll have to explain what you mean as distinct. Getting the min and max for each variable is easy.

Contributor
Posts: 60

Re: min and max value in once

I want to see what is the count of distinct value in source - target. and also want to see all the values moving from source target or not.

It's a kind of data quality test.

Let me know how to get min and max value in one shot of 100+ variables. Please keep in mind data having char as well as num

Message was edited by: Pravin Mishra

Solution
‎10-18-2014 04:49 PM
PROC Star
Posts: 7,433

Re: min and max value in once

proc sql noprint;

  select catt('min(',name,') as min_',name,

   ', max(',name,') as max_',name)

    into :select separated by ','

      from dictionary.columns

        where libname='SASHELP' and

              memname='CLASS'

  ;

  create table want as

    select &select.

      from sashelp.class

  ;

quit;

Valued Guide
Posts: 2,177

Re: min and max value in once

PravinMishra

If you also want a count of distinct values for each colum in Art's output table, then extend Art's code with

', count( distinct ', name, ') as dct_', name,

Beware of this general approach if you have column names wider than 28 characters. Prefixed like the above endangers the 32char width rule for column names.

Truncation to 32 chars would be ok only if you have no risk of duplicates (where two column names only differ after the 28th character).

If you need to solve this generate unique names with the VARNUM column rather than NAME. At the same time you can provide all these details in the LABEL attribute.

But you might not need that at all.

Good luck

peterC

PROC Star
Posts: 7,433

Re: min and max value in once

: Excellent suggestion, but I think that catt can't be used given that a trailing space would be needed. The following, however, will produce all 3 desired values for each variable:

proc sql noprint;

  select cat('min(',strip(name),') as min_',strip(name),

   ', max(',strip(name),') as max_',strip(name),

    ',count( distinct ', strip(name), ') as dct_', strip(name))

    into :select separated by ','

      from dictionary.columns

        where libname='SASHELP' and

              memname='CLASS'

  ;

  create table want as

    select &select.

      from sashelp.class

  ;

quit;

Valued Guide
Posts: 2,177

Re: min and max value in once

Art

You could support CATT() with this trick. Rather than 'count( distinct ' where the trailing blank will be lost, try

'count( distinct %str( )'

Because the %str() is within single quotes it should not be resolved when catt() trims parameters. It should resolve when the generated macro variable of sql syntax is resolved.

PROC Star
Posts: 7,433

Re: min and max value in once

: Absolutely and much appreciated! I'll have to remember that!

Contributor
Posts: 60

Re: min and max value in once

Thanks Peter for your suggestion.

Contributor
Posts: 60

Re: min and max value in once

Thanks Arthur for your suggestion.it really work for me

Trusted Advisor
Posts: 1,228

Re: min and max value in once

What I understood based on your description, you are trying to move your data from source (e.g. sashelp.class) to target (e.g. work.class) and just wanted to make sure data moved correctly. Below code will provide min and max values for all numeric variables based on source and target side by side for comparison.

data work.class; /* Target */
set sashelp.class;
run;

data have;
set sashelp.class (in=source) work.class (in=target);
if source then flag='source';
if target then flag='target';
run;

proc sort data=have;
by flag;
run;

proc tabulate data=have;
class flag;
var _numeric_;
table _numeric_,(min max)*flag=' ';
run;

Regular Contributor
Posts: 184

Re: min and max value in once

If that is the case, I recommend PROC COMPARE.

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 13 replies
  • 536 views
  • 3 likes
  • 5 in conversation