macro error

[manya92]

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. 

[PaigeMiller · Accepted solution]

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

[Tom]

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.

[RW9]

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 ;

[PaigeMiller]

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.

[manya92]

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

1

F012DC0F325EF

12/31/2014

12/31/2014

160

2

F012DC0F325EF

12/31/2014

12/31/2014

194

3

F012DC0F325EF

12/31/2014

12/31/2014

220

4

F012DC0F325EF

12/31/2014

12/31/2014

230

5

F012DC0F325EF

12/31/2014

12/31/2014

230

6

F012DC0F325EF

12/31/2014

12/31/2014

235

7

F012DC0F325EF

12/31/2014

12/31/2014

250

and when i run select ,  i see this 

1

F012DC0F325EF

12/31/2014

04/28/2015

240

2

F012DC0F325EF

12/31/2014

04/08/2015

289

3

F012DC0F325EF

12/31/2014

03/16/2015

290

4

F012DC0F325EF

12/31/2014

03/25/2015

289

5

F012DC0F325EF

12/31/2014

01/21/2015

238

6

F012DC0F325EF

12/31/2014

03/23/2015

180

do you know why this is happening ?

[ballardw]

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;