SAS 9.4 TS1M5
Hello,
I am trying to figure out how to run multiple distinct strings of data and proc sql steps based on previous criteria found within a couple of previous proc sql steps. Below are a couple of example steps that I'd like to use as the criteria.
proc sql;
create table ft_count as
select count(distinct id) as count
from missing_ft;
quit;
proc sql;
create table pt_count as
select count(distinct id) as count
from missing_pt;
quit;
What I'd like to be able to do is view the counts as criteria that defines how the script proceeds. In this example, there would be three possibilities for the criteria:
1. A value greater than 0 in ft_count and a value equal to 0 in pt_count
2. A value greater than 0 in pt_count and a value equal to 0 in ft_count
3. A value greater than 0 in both ft_count and pt_count
What I am trying to do is to write a script that proceeds with three distinct paths that include a variety of data or proc sql steps based on each of the three possibilities above. I have a feeling that this might require some if, then, or do statements and I have read up on them, but given that this is my first foray into that territory I have thus far just been lost. If need be I can provide the further proc sql steps although I'm not certain they are really necessary for answering the question, so I will hold off on that for now.
Thank you.
Evan
There are several ways you can do this in SAS. One of the simplest ways could be like this:
data _null_;
set have;
if ft_count > 0 and pt_count = 0 then
call execute('%include "MyProg1.sas";');
else if ft_count = 0 and pt_count > 0 then
call execute('%include "MyProg2.sas";');
else if ft_count > 0 and pt_count > 0 then
call execute('%include "MyProg3.sas";');
run;
Here I'm assuming you have stored your conditional code in three separate programs and you have the FT_COUNT and PT_COUNT variables in a dataset called HAVE. Using CALL EXECUTE controls which of your 3 programs gets run.
There are several ways you can do this in SAS. One of the simplest ways could be like this:
data _null_;
set have;
if ft_count > 0 and pt_count = 0 then
call execute('%include "MyProg1.sas";');
else if ft_count = 0 and pt_count > 0 then
call execute('%include "MyProg2.sas";');
else if ft_count > 0 and pt_count > 0 then
call execute('%include "MyProg3.sas";');
run;
Here I'm assuming you have stored your conditional code in three separate programs and you have the FT_COUNT and PT_COUNT variables in a dataset called HAVE. Using CALL EXECUTE controls which of your 3 programs gets run.
Thank you! This looks to be what I need. I will take a look into using this for my code momentarily.
If you are using a very recent SAS version then you can also use open code macro language for this as shown below.
For less recent versions you would need to wrap a macro definition around the code.
Using SAS Macro code is a powerful option but it's something you should only start using once you're really familiar with Base SAS coding.
proc sql noprint;
select count(*) into :row_cnt trimmed
from sashelp.class
;
quit;
%if &row_cnt<=10 %then
%do;
data _null_;
put "row count <=10. It is &row_cnt";
stop;
run;
%end;
%else
%do;
data _null_;
put "row count is &row_cnt";
stop;
run;
%end;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.