Loop using Counts in PROC SQL

[yelena]

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!

[ballardw]

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.

[yelena]

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

[PGStats · Accepted solution]

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;

[yelena]

Hello, thank you very much! It actually works now! Yay!

[yelena]

And to estimate it across time, should I just add Year variable?

[PGStats]

Yes

keep year variable value;

...

select year, variable, ...

...

group by year, variable

[yelena]

Perfect! It works now! Thank you so much!