[prev in list] [next in list] [prev in thread] [next in thread] 

List:       sas-l
Subject:    Re: Is there any quick way of getting the variable list in a data set?
From:       "Keintz, H. Mark" <mkeintz () WHARTON ! UPENN ! EDU>
Date:       2012-07-31 18:33:38
Message-ID: E0B423A8C0D1E74B8905B2C5CB38C1AF03010011 () GENO4 ! wharton ! upenn ! edu
[Download RAW message or body]

Simple yes, but while the MIN and MAX functions will accept character vars (i.e. \
country, currency_code could easily be character), while the SUM will stop the SQL.

Perhaps the OP used MIN and MAX functions even on vars that were constant for a given \
account number, so that each GROUP BY level would yield only one record each \
(otherwise you'll get the original number of records).  In any case, I can't think of \
any other reason one would select  max(currency_code).

If that's the reason, it would probably be better solved by dropping the MIN or MAX \
for constant vars, and either use "select distinct":

   select  distinct  max(date) as date,  country, branch_number, .....  group by \
account_number ...

or add those vars to the group by clause:

  select max(date) as date, country, branch_number .... group by account_number, \
country, branch_number ...


Regards,
Mark

-----Original Message-----
From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU] On Behalf Of Bolotin Yevgeniy
Sent: Tuesday, July 31, 2012 1:59 PM
To: SAS-L@LISTSERV.UGA.EDU
Subject: Re: Is there any quick way of getting the variable list in a data set?

This will produce a much bigger resultant dataset, but makes the code simpler to \
write and the output easier to understand/use...


/* replace WORK and HAVE with whatever library/dataset you actually have */


proc sql noprint;
    select name
        into : colList separated by ' '
    from dictionary.columns
    where libname = "%upcase(WORK)" and
          memname = "%upcase(HAVE)"
          and lowcase(name) not in ('account_number')
          and type = 'num' /* you don't want to aggregate char variables, I assume */
        ;
quit;

%let cols_n = &sqlobs.;

%macro z;

Proc sql;
Create table WANT as
Select
  Account_number
  %do i = 1 %to & cols_n.;
    %let var=%scan(&colList., &i.);
    , min(&var.) as min_&var.
    , max(&var.) as max_&var.
    , sum(&var.) as sum_&var.
  %end;
  From HAVE
  Group by account_number;
Quit;
%mend;



-----Original Message-----
From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU] On Behalf Of Sigurd W. \
                Hermansen
Sent: Tuesday, July 31, 2012 1:48 PM
To: SAS-L@LISTSERV.UGA.EDU
Subject: Re: Is there any quick way of getting the variable list in a data set?

Mirisage:
You have several good solutions for your original problem. For a more complex problem \
involving many CASE WHEN clauses or the like, I would use a Data step to read a list \
of variables and write a SAS SQL query to a text file (<program>.sas). You can then \
INCLUDE the text file to execute it.

I should mention, though, that the summary functions in the example query that you \
posted will collapse multiple rows with the same account_number to a single row. The \
minimum account_number in an account_number group will be the one and only \
account_number in that group. I'd specify the account_number as an attribute, not the \
minimum value. Less confusing. S
> > > 
I am going to create a sql code like below.

proc sql;
   create table want as
      select
         min(bank_number) as bank_number,
         min (account_number) as account_number,
         min(country) as country,
         min(branch_number) as branch_number,
         min(currency_code) as currency_code,
         max (post_date) as post_date format=date9.,
         max (effective_date) as effective_date format=date9.,
         sum (principal) as sum_principal,
         sum (write_off_total) as net_write_off_total
      from
         fromweb.post_this_data
      group by
         account_number
      ;
quit;
<<<




On Tue, 31 Jul 2012 14:24:40 +0000, Keintz, H. Mark <mkeintz@WHARTON.UPENN.EDU> \
wrote:

> Mirisage:
> 
> If you only wanted one function, like "min", it would be pretty
straightforward to first issue a
> "select distinct  ..... into :mysel from dic tionary.columns ...
order by varnum"
> 
> Then you could use MYSEL in a
> "create table want as select &mysel ..."
> statement.
> 
> But you need different functions for different variables.  I suppose
the "select ... into:" could have a lot of CASE expressions to help you through this, \
but I'd go with a DATA step using sashelp.vcolumn. Something like (untested):
> 
> 
> %LET sum_vars="PRINCIPAL","WRITE_OFF_TOTAL";
> 
> data need;
> set sashelp.vtable ;
> where libname='FROMWEB' and memname='POST_THIS_DATA';
> 
> if upcase(scan(name,-1,'_'))='DATE' then do;
> fn='max';  fmt='format=date9.';
> end;
> else if upcase(name) in (&sum_vars) then fn='sum';  else fn='min';
> 
> txt =     cats(fn ,'(', name ,') as' )   || ' '   ||     cats
(name,fmt) ;
> run;
> 
> This make the PROC SQL part really simple:
> 
> proc sql;
> select distinct trim(txt) into  :mysel separated by ',' from need
order by varnum;
> 
> create table want as select &mysel from ..... group by
> account_number; quit;
> 
> 
> This assume everything gets a MIN function except variables in SUM_VARS
or variables whose name ends in "_DATE".
> 
> Regards,
> Mark
> 
> 
> -----Original Message-----
> From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU] On Behalf Of
Mirisage Fernando
> Sent: Tuesday, July 31, 2012 8:56 AM
> To: SAS-L@LISTSERV.UGA.EDU
> Subject: Re: Is there any quick way of getting the variable list in a
data set?
> 
> Hi Joe, Ronald and Mary,
> Thanks everyone of you for this great help. Your contribution has
provided me an additional stimulation to explore about data dictionaries.
Thanks for it.
> 
> Hi Mary,
> Yes, please share your code with me.
> 
> What I need is this.
> 
> I am going to create a sql code like below.
> 
> proc sql;
> create table want as
> select
> min(bank_number) as bank_number,
> min (account_number) as account_number,
> min(country) as country,
> min(branch_number) as branch_number,
> min(currency_code) as currency_code,
> max (post_date) as post_date format=date9.,
> max (effective_date) as effective_date format=date9.,
> sum (principal) as sum_principal,
> sum (write_off_total) as net_write_off_total
> from
> fromweb.post_this_data
> group by
> account_number
> ;
> quit;
> 
> 
> I have dozens of variables in my data set. For e.g. bank_number,
account_number etc. I need to cut and paste each one of these variables to the \
proposed SQL code if I use "proc contentes".
> So,what I need is some method to generate a simple list of complete set
of varaibles in the data set in the same order as in data set.
> 
> E.g.
> bank_number
> account_number
> account_number
> country
> 
> and so on.....
> 
> Thanks
> 
> Mirisage


[prev in list] [next in list] [prev in thread] [next in thread]