topic macros in SAS Programming

macros

manya92 — Thu, 05 Jul 2018 15:17:30 GMT

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 ;

Re: macros

novinosrin — Thu, 05 Jul 2018 15:19:01 GMT

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

Re: macros

manya92 — Thu, 05 Jul 2018 15:21:47 GMT

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.

Re: macros

RW9 — Thu, 05 Jul 2018 15:22:18 GMT

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

Re: macros

PaigeMiller — Thu, 05 Jul 2018 15:22:30 GMT

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

Re: macros

RW9 — Thu, 05 Jul 2018 15:25:51 GMT

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.

Re: macros

novinosrin — Thu, 05 Jul 2018 15:27:48 GMT

@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;

Re: macros

manya92 — Thu, 05 Jul 2018 15:29:21 GMT

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;

Re: macros

manya92 — Thu, 05 Jul 2018 15:32:29 GMT

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;

Re: macros

RW9 — Thu, 05 Jul 2018 15:35:57 GMT

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.

Re: macros

Tom — Thu, 05 Jul 2018 15:53:21 GMT

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;

Re: macros

manya92 — Thu, 05 Jul 2018 15:51:58 GMT

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 ;

Re: macros

Tom — Thu, 05 Jul 2018 15:54:59 GMT

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.

Re: macros

manya92 — Thu, 05 Jul 2018 15:58:54 GMT

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;

Re: macros

Tom — Thu, 05 Jul 2018 15:59:24 GMT

If you want to make the macro more flexible add parameters. You can include logic to add a default value.

You can have SAS add the default value for you if you make the parameters non-positional.

%macro patient_obs(dsn=&syslast);
....
  from &dsn 
....
%mend ;

Or you can add your own logic.

%macro patient_obs(dsnt);
%if 0=%length(&dsn) %then %let dsn=&syslast;
%if not %sysfunc(exist(&dsn)) %then %do;
  %put &=dsn not found.;
%end;
....
  from &dsn 
....
%mend ;

Re: macros

Tom — Thu, 05 Jul 2018 16:00:03 GMT

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

Re: macros

manya92 — Thu, 05 Jul 2018 17:11:58 GMT

Thank You

Re: macros

manya92 — Thu, 05 Jul 2018 17:27:21 GMT

in this code :- what is INTO:npatients trimmed nobs trimmed for ? i dont know what trimmed means in this case

%macro patient_obs() ;
%global nobs npatients ;
proc sql noprint;
  select
         count(enrolid) format=32.
       , count(N) format=32.
    into 
         :npatients trimmed 
       , :nobs trimmed
  from &syslast. 
  ;
quit ;
%put Dataset &syslast. has &nobs. observations and &npatients. patients ;
%mend patient_obs;

Re: macros

Tom — Thu, 05 Jul 2018 18:03:41 GMT

Apparently the new SAS 9.4 documentation is defective.

Look at the SAs 9.3 documentation instead.

INTO clause