ok. can you back up a bit. what is the best way to get the value counts from drug1 assuming we know the var_lst ? your second proc sql from your original post is creating separate variable. Is there quick step to get a list of values from separate variables? I got the list for the expected values but it took a few steps to get each value then concatenate them into a column and then used proc sql with into: to get the list. Originally when I got to the step where I needed to determine the condition (actual < expected) I was getting the actual variable by variable. Your newest proc sql sounds like what I need but I first need to get the full list of actuals.
I'm not certain that creating a list of actual values is necessary. But if it is, here is a way it can be done. Assuming that macro variables already exist as detailed earlier (&VARCount=3, &VAR_LST=age gender type, plus &AGE, &GENDER, and &TYPE holding actual counts):
%local actual_counts;
%do k=1 %to &varCount;
%let next_name = %scan(&var_lst, &k);
%let actual_counts = &actual_counts &&&next_name;
%end;
only the expected variable list and expected values exist (the actual variable list starts out equal to the expected variable list in this example). The actual number of distinct values/levels for each variable in the drug1 dataset needs to be created. If a condition is met (actual < expected) for that variable then the associated variable needs to be removed from the original list.
Here's an approach that skips part of the list creation. It builds a smaller list ... just those variable names that have actual values less than expected values. Start with your original 3 %LET statements. Then ...
%local less_than_expected;
proc sql;
%do k=1 %to &varCount;
select count(distinct %scan(&var_lst, &k)) into : actual from drug1;
%if &actual < %scan(&expected, &k) %then %let less_than_expected = &less_than_expected %scan(&var_lst, &k);
%end;
quit;
I'm not sure if I'm helping by showing this or not. Basically, the SELECT statement in SQL executes immediately, and so &ACTUAL is immediately available for use in the next statement. It doesn't work that way when you switch from SQL to a DATA step, however.
Are we getting closer to the target here? I realize we still need to utilize &LESS_THAN_EXPECTED.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.