topic Re: Loop using Counts in PROC SQL in SAS Programming

Loop using Counts in PROC SQL

yelena — Fri, 08 Jul 2016 20:29:05 GMT

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!

Re: Loop using Counts in PROC SQL

ballardw — Fri, 08 Jul 2016 20:43:46 GMT

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.

Re: Loop using Counts in PROC SQL

PGStats — Fri, 08 Jul 2016 21:20:14 GMT

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;

Re: Loop using Counts in PROC SQL

yelena — Fri, 08 Jul 2016 21:22:25 GMT

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

Re: Loop using Counts in PROC SQL

yelena — Fri, 08 Jul 2016 21:31:15 GMT

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

Re: Loop using Counts in PROC SQL

yelena — Fri, 08 Jul 2016 21:58:49 GMT

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

Re: Loop using Counts in PROC SQL

PGStats — Fri, 08 Jul 2016 22:05:56 GMT

Yes

keep year variable value;

...

select year, variable, ...

...

group by year, variable

Re: Loop using Counts in PROC SQL

yelena — Fri, 08 Jul 2016 22:28:46 GMT

Perfect! It works now! Thank you so much!