BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
yelena
Fluorite | Level 6

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!

1 ACCEPTED SOLUTION

Accepted Solutions
PGStats
Opal | Level 21

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

7 REPLIES 7
ballardw
Super User

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
Fluorite | Level 6

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
Opal | Level 21

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
yelena
Fluorite | Level 6

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

yelena
Fluorite | Level 6

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

PGStats
Opal | Level 21

Yes

 

keep year variable value;

...

select year, variable, ...

...

group by year, variable

PG
yelena
Fluorite | Level 6

Perfect! It works now! Thank you so much!

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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