I am trying to create a loop across variables in PROC SQL, but my code does not work. I know how to do it manually:
proc freq data=sashelp.class nlevels;
tables name age sex;
title 'Number of distinct values for each variable';
run;
/* Using PROC SQL to count the number of levels for a variable. */
proc sql;
create table new1 as
select count(distinct(name)) as namecount,
count(distinct(age)) as agecount,
count(distinct(sex)) as sexcount
from sashelp.class;
quit;
But I am trying to come up with the way to use the loop across variables, such as:
% let list =
age
height
weight
;
data sql.count_test;
set sashelp.class;
array var_list(*) &list;
do i=1 to dim(var_list);
variable=var_list(i);
output;
end;
proc sql;
create table new2 as
select count(distint(variable)) as variable_count
from sql.count_test;
quit;
Now, how the program should be modified if we also have a date component?
Thank you for your help!
I guess you are looking for something like:
/* List of numeric variables (possibly including dates) */
%let list =
age
height
weight
;
/* Transpose observations */
data classValues;
length variable $32;
set sashelp.class;
array var_list(*) &list;
do i=1 to dim(var_list);
variable=vname(var_list{i});
value = var_list{i};
output;
end;
keep variable value;
run;
/* Count distinct values for each variable */
proc sql;
create table varCounts as
select variable, count(distinct value) as valueCount
from classValues
group by variable;
select * from varCounts;
quit;
Can you provide what you expect the output to be for that last Proc Sql code?
And then what you mean by an included date? I have no clue what that may mean.
Thank you for checking my post.
the output I'd like to see should look like this:
agecount = 6
heightcount = 17
weightcount = 15
But now I assume I have
Year Name Age Height Weight
2005 Alfred 14 69.0 112.5
2005 Alice 13 56.5 84.0
2005 Barbara 13 65.3 98.0
2006 Alfred 15 69.0 120.0
2006 Alice 14 56.5 82.0
2006 Janet 15 62.5 112.5
How can I estimate the counts across time?
Thank you.
Yelena
I guess you are looking for something like:
/* List of numeric variables (possibly including dates) */
%let list =
age
height
weight
;
/* Transpose observations */
data classValues;
length variable $32;
set sashelp.class;
array var_list(*) &list;
do i=1 to dim(var_list);
variable=vname(var_list{i});
value = var_list{i};
output;
end;
keep variable value;
run;
/* Count distinct values for each variable */
proc sql;
create table varCounts as
select variable, count(distinct value) as valueCount
from classValues
group by variable;
select * from varCounts;
quit;
Hello, thank you very much! It actually works now! Yay!
And to estimate it across time, should I just add Year variable?
Yes
keep year variable value;
...
select year, variable, ...
...
group by year, variable
Perfect! It works now! Thank you so much!
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.