DATA Step, Macro, Functions and more

Loop using Counts in PROC SQL

Accepted Solution Solved
Reply
Contributor
Posts: 41
Accepted Solution

Loop using Counts in PROC SQL

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!


Accepted Solutions
Solution
‎07-08-2016 05:30 PM
Respected Advisor
Posts: 4,931

Re: Loop using Counts in PROC SQL

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;
PG

View solution in original post


All Replies
Super User
Posts: 11,343

Re: Loop using Counts in PROC SQL

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.

Contributor
Posts: 41

Re: Loop using Counts in PROC SQL

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

Solution
‎07-08-2016 05:30 PM
Respected Advisor
Posts: 4,931

Re: Loop using Counts in PROC SQL

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;
PG
Contributor
Posts: 41

Re: Loop using Counts in PROC SQL

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

Contributor
Posts: 41

Re: Loop using Counts in PROC SQL

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

Respected Advisor
Posts: 4,931

Re: Loop using Counts in PROC SQL

Yes

 

keep year variable value;

...

select year, variable, ...

...

group by year, variable

PG
Contributor
Posts: 41

Re: Loop using Counts in PROC SQL

Perfect! It works now! Thank you so much!

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 7 replies
  • 599 views
  • 1 like
  • 3 in conversation