topic Re: How do we create both distinct variables and number of variable counts in sql macro in SAS Procedures

How do we create both distinct variables and number of variable counts in sql macro

gsk — Sat, 14 Apr 2018 03:14:06 GMT

I want to create macro variables of lists of numeric, character, and date variable (separately) and their respective number of variables. Below is my code so far but I don't know how to add codes to create macros to store the number of each variable type.

proc sql noprint;
  select name into :numlist separated by ' ',
/ * HERE.. I don't know what to write. something like len(&numlist) or count(name) into :numvalcount is what I want to do. */
          from dictionary.columns
          where libname='MET' and memname='DEMA_669' and
                      type='num' AND FORMAT^='DATE9.'
          order by name;

select name into :datelist separated by ' ',

/ * HERE.. I don't know what to write. */
          from dictionary.columns
          where libname='MET' and memname='DEMA_669' and
                      type='num' AND FORMAT='DATE9.'
          order by name;

select name into :charlist separated by ' ',

/ * HERE.. I don't know what to write. */
          from dictionary.columns
          where libname='MET' and memname='DEMA_669' and
                      type='char'
          order by name;
quit;

So in the end I want to have:

%put &numlist;

%put &charlist;

%put &datelist;

%put &numvalcount;

%put &charvalcount;

%put &datevalcount;

Re: How do we create both distinct variables and number of variable counts in sql macro

kiranv_ — Sat, 14 Apr 2018 03:56:13 GMT

simplest I can think of is

proc sql noprint;
select name into
    :numlist SEPARATED by ' '
  from dictionary.columns
            where libname='SASHELP' and memname='CARS' and
                        type='num' AND FORMAT^='DATE9.'
order by name;

%let numvalcount= %sysfunc(countw(&numlist));


%put value of numeric columns is &numlist ;
%put value of count columns is &numvalcount;

Re: How do we create both distinct variables and number of variable counts in sql macro

PGStats — Sat, 14 Apr 2018 05:00:00 GMT

This syntax works:

proc sql noprint;
select name, count(name) into :numlist separated by ' ', :numNumber 
from dictionary.columns
where libname='SASHELP' and memname='CLASS' and
type='num' AND FORMAT ne 'DATE9.'
order by name;

%put &numlist;
%put &numNumber;

Re: How do we create both distinct variables and number of variable counts in sql macro

Shmuel — Sat, 14 Apr 2018 05:54:31 GMT

You may even try by:

proc freq data=sashelp.vcolumn
              (where=(libname='MET' and memname='DEMA_669'));
        table type * format;
run;

Re: How do we create both distinct variables and number of variable counts in sql macro

Astounding — Sat, 14 Apr 2018 21:27:53 GMT

SQL does this for you. You just have to capture the information before it disappears:

%let numvalcount = &sqlobs;

select name into :datelist separated by ' ',

/ * HERE.. I don't know what to write. */
          from dictionary.columns
          where libname='MET' and memname='DEMA_669' and
                      type='num' AND FORMAT='DATE9.'
          order by name;

%let datevalcount = &sqlobs;

select name into :charlist separated by ' ',

/ * HERE.. I don't know what to write. */
          from dictionary.columns
          where libname='MET' and memname='DEMA_669' and
                      type='char'
          order by name;

%let charvalcount = &sqlobs;
quit;