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;
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;
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;
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;
You may even try by:
proc freq data=sashelp.vcolumn
(where=(libname='MET' and memname='DEMA_669'));
table type * format;
run;
SQL does this for you. You just have to capture the information before it disappears:
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;
%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;
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.