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

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 ;

1 ACCEPTED SOLUTION

Accepted Solutions
Tom
Super User Tom
Super User

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;

 

View solution in original post

18 REPLIES 18
novinosrin
Tourmaline | Level 20

Can you provide a sample of an INPUT data and your expected OUTPUT sample explaining your requirement plz?

manya92
Fluorite | Level 6

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. 

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

RW9
Diamond | Level 26 RW9
Diamond | Level 26
%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.

PaigeMiller
Diamond | Level 26

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).

--
Paige Miller
manya92
Fluorite | Level 6

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

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; 
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.  

novinosrin
Tourmaline | Level 20

@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;
Tom
Super User Tom
Super User

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;

 

manya92
Fluorite | Level 6

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 ;
Tom
Super User Tom
Super User

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.

manya92
Fluorite | Level 6

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;
Tom
Super User Tom
Super User

Do not use a variable name there.  Use either * or 1.  I updated the earlier post.

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
  • 18 replies
  • 1385 views
  • 3 likes
  • 5 in conversation