topic Re: macro error in SAS Programming

macro error

manya92 — Wed, 19 Sep 2018 15:10:06 GMT

Hi everyone ,

I ran this code which has

%macro drug ;
	
%do i = 1 %to 3 ;
	%let name=%scan(x*y*z,&i,*) ;
	
	proc sql;
		create table want as
		select distinct a.PatientID,a.index_date,a.last_trt_date
					,b.AdministeredDate as  admn_date format mmddyy10. 
					,b.AdministeredAmount as dose
					,b.AdministeredUnits
					,b.OrderID
					,b.commondrugname
		from have as  a 
		inner join dfi_panc.medicationadmin as b 
		on a.PatientID = a.PatientID  
		inner join data.a as  c 
		on b.PatientID = c.PatientID and b.OrderID =c.OrderID
		and lowcase(b.CommonDrugName) = "&name." 
		and a.index_date le b.AdministeredDate le a.last_trt_date
		and b.AdministeredAmount ne .
		and b.AdministeredDate ne .
		order by a.PatientID;
quit ;

%end;
%mend drug;
%drug;

no error but i am only able to see the results of only one drug. Any suggestions on how to see all drugs.

Re: macro error

PaigeMiller — Wed, 19 Sep 2018 15:14:54 GMT

Each time the loop runs, it creates table WANT and overwrites any previous table WANT, so you can't see the previous results.

To fix this, change the CREATE line to

create table want&name as

Re: macro error

Tom — Wed, 19 Sep 2018 15:15:28 GMT

You are reusing the same table name for three different queries.

The later runs overwrite the previous one.

You need to explain what you want. Providing sample input data and expected output data would help also.

Re: macro error

RW9 — Wed, 19 Sep 2018 15:18:04 GMT

Think about it another way:

Run the proc sql once to merge all the data, then filter off what you don't want (also is working with separate datasets containing the same information a good way forward, I would suspect not);

	proc sql;
		create table want as
		select distinct a.PatientID,a.index_date,a.last_trt_date
					,b.AdministeredDate as  admn_date format mmddyy10. 
					,b.AdministeredAmount as dose
					,b.AdministeredUnits
					,b.OrderID
					,b.commondrugname
		from have as  a 
		inner join dfi_panc.medicationadmin as b 
		on a.PatientID = a.PatientID  
		inner join data.a as  c 
		on b.PatientID = c.PatientID and b.OrderID =c.OrderID
		and lowcase(b.CommonDrugName) in ("x","y","z") 
		and a.index_date le b.AdministeredDate le a.last_trt_date
		and b.AdministeredAmount ne .
		and b.AdministeredDate ne .
		order by a.PatientID;
quit ;

Re: macro error

PaigeMiller — Wed, 19 Sep 2018 15:24:50 GMT

I add that it doesn't seem that you need macros here at all, you could do one SQL and get the results for all three drugs into one data set. This is a much simpler result, and more usable too, in case future analyses are needed, you can use a BY statement, rather than running code to execute on WANTA and then more code to execute on WANTB and more code to execute on WANTC.

If I were you @manya92, I would seriously consider doing it without macros and having all drug data in one data set.

Re: macro error

manya92 — Wed, 19 Sep 2018 16:02:36 GMT

So i have another query : when i run the same code without select DISTINCT i get differernt administered dates than when i use DISTINCT. So to be clear i see this when i run

F012DC0F325EF

12/31/2014

160

F012DC0F325EF

12/31/2014

194

F012DC0F325EF

12/31/2014

220

F012DC0F325EF

12/31/2014

230

F012DC0F325EF

12/31/2014

230

F012DC0F325EF

12/31/2014

235

F012DC0F325EF

12/31/2014

250

and when i run select , i see this

F012DC0F325EF

12/31/2014

04/28/2015

240

F012DC0F325EF

12/31/2014

04/08/2015

289

F012DC0F325EF

12/31/2014

03/16/2015

290

F012DC0F325EF

12/31/2014

03/25/2015

289

F012DC0F325EF

12/31/2014

01/21/2015

238

F012DC0F325EF

12/31/2014

03/23/2015

180

do you know why this is happening ?

Re: macro error

ballardw — Wed, 19 Sep 2018 20:13:38 GMT

SELECT DISTINCT should yield one observation for the combination of variables. Without distinct you get all of the records that meet any WHERE conditions involved.

Perhaps an example with a simple query and a small data set will help:

Proc sql;
   title 'with distinct' ;
   select distinct sex
   from sashelp.class
   ;
   title 'without distinct';
   select sex 
   from sashelp.class;
quit;title;