how to create a macros that counts patient number and observation number for each dataset ?
For eg. this is what i made , but it doesnt work
%macro willthismacrowork(yn) ; proc sql ; select count(enrolid) , count(obs) from &syslast. ; quit ; %put yn ; %mend willthismacrowork(yn=yes) ; %willthismacrowork ;
The macro will run, but I don't think the SQL query is doing what you really want.
Try this instead.
%macro patient_obs() ;
%global nobs npatients ;
proc sql noprint;
select
count(distinct enrolid) format=32.
, count(*) format=32.
into
:npatients trimmed
, :nobs trimmed
from &syslast.
;
quit ;
%put Dataset &syslast has &nobs observations and &npatients patients ;
%mend patient_obs;
Can you provide a sample of an INPUT data and your expected OUTPUT sample explaining your requirement plz?
For eg, i have different datasets , some of whihc are one row per patient and some are multiple rows per patient. Now instead of writing the whole code like :-
proc sql ; select count(enrolid), count(obs) from have ; quit ;
i wish to make a MACROS that i have to just call to print this for me each time i want to see the number instead of having to type all that code.
This question is making less sense. You want to create a macro to print out number of observations in a dataset? Look at the look, when you use a dataset it prints how many observations are in the dataset. Seems to me to just be creating work for no purpose.
%macro willthismacrowork(ds=); proc sql ; select count(enrolid), count(obs) from &ds.; quit; %mend willthismacrowork; %willthismacrowork;
I would suggest that if you are starting to develop general code you start by documenting it rather than coding. Using &syslast. is a pretty bad idea, what if you want to do this several times on different datasets for a start?
Posting "but it doesnt work" does not tell us anything, what doesn't work, logs with errors, incorrect logic, etc.
but it doesnt work
Why do you say that? Explain! Provide information! Show us the SASLOG (use the {i} icon and paste relevant portions of the SASLOG into the box that appears).
I made a MACROS earlier like below :
%macro print ; proc print data = &syslast. (obs=10) ; run ; %mend print ; %print ;
So whenever i want to print a dataset i simply CALL THE MACROS
%print;
Please read this in continuation with the previous text
Now i wish to create a similar macros that will print the number of distinct patients for me and the numebr of observations so i can see if my dataset is unique p by patient id or not
For eg. something like this :-
%macro obs ; proc sql ; select count(enrolid), count(obs) from &syslast. ; quit ; %mend obs ; %obs;
You are running before you can walk in this and your other post. Forget macros, learn how to do things in Base SAS - this is the programming language. Avoid using &syslast., I understand it is used on one of the SAS systems, but personally I find it very bad coding, and if you go into general code then you could end up with all kinds of issues.
Why do you not know if your dataset is distinct? Did you not create it? Obs is printed to the log each time.
@manya92 The community would like to see a nice data sample . Just words will only delay and will keep going back and forth.
Are you after this?
The sample i use is sashelp.class dataset
proc sql;
create table want as
select count(*) as gender_count,( select count(*) from sashelp.class) as obs_count
from sashelp.class
group by sex;
quit;
The macro will run, but I don't think the SQL query is doing what you really want.
Try this instead.
%macro patient_obs() ;
%global nobs npatients ;
proc sql noprint;
select
count(distinct enrolid) format=32.
, count(*) format=32.
into
:npatients trimmed
, :nobs trimmed
from &syslast.
;
quit ;
%put Dataset &syslast has &nobs observations and &npatients patients ;
%mend patient_obs;
So when i run this MACROS theres no error but when i call this MACROS to give me the number of patients and observations it gives me this error :-
1 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK; 72 73 %patient_obs ; ERROR: The following columns were not found in the contributing tables: N. NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements. NOTE: The SAS System stopped processing this step because of errors. NOTE: PROCEDURE SQL used (Total process time): real time 0.00 seconds cpu time 0.00 seconds Dataset WORK._01_ELIG has observations and patients 74 75 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK; 88
Also, this is what is the code for which the above error appeared :-
data derived._01_cohort1 ; set _01_age ; run ;/*12316*/ %patient_obs ;
I am not sure if SYSLAST is remember across submissions when using SAS/Studio or Enterprise Guide.
Also there is no reference to N in the macro I posted, so you are perhaps running something different?
You can turn on the MPRINT option to see the SAS code that the macro has generated.
Even when i wrote this :- , it says OBS variable not found in &syslast.
%macro patient_obs() ;
%global nobs npatients ;
proc sql noprint;
select
count(distinct enrolid) format=32.
, count(obs) format=32.
into
:npatients trimmed
, :nobs trimmed
from &syslast.
;
quit ;
%put Dataset &syslast. has &nobs. observations and &npatients. patients ;
%mend patient_obs;
Do not use a variable name there. Use either * or 1. I updated the earlier post.
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 16. 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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: