DATA Step, Macro, Functions and more

macros

Accepted Solution Solved
Reply
Contributor
Posts: 53
Accepted Solution

macros

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 ;


Accepted Solutions
Solution
2 weeks ago
Super User
Super User
Posts: 8,127

Re: macros

[ Edited ]

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


All Replies
PROC Star
Posts: 1,838

Re: macros

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

Contributor
Posts: 53

Re: macros

Posted in reply to novinosrin

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. 

Super User
Super User
Posts: 9,599

Re: macros

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.

Super User
Super User
Posts: 9,599

Re: macros

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

Respected Advisor
Posts: 3,066

Re: macros

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
Contributor
Posts: 53

Re: macros

Posted in reply to PaigeMiller

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;
Contributor
Posts: 53

Re: macros

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; 
Super User
Super User
Posts: 9,599

Re: macros

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.  

PROC Star
Posts: 1,838

Re: macros

@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;
Solution
2 weeks ago
Super User
Super User
Posts: 8,127

Re: macros

[ Edited ]

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;

 

Contributor
Posts: 53

Re: macros

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 ;
Super User
Super User
Posts: 8,127

Re: macros

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.

Contributor
Posts: 53

Re: macros

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;
Super User
Super User
Posts: 8,127

Re: macros

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

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 18 replies
  • 137 views
  • 3 likes
  • 5 in conversation