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

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. 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
PaigeMiller
Diamond | Level 26

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
--
Paige Miller

View solution in original post

6 REPLIES 6
PaigeMiller
Diamond | Level 26

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
--
Paige Miller
Tom
Super User Tom
Super User

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

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

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.

--
Paige Miller
manya92
Fluorite | Level 6

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

 


 
 
1F012DC0F325EF12/31/201412/31/2014160 
2F012DC0F325EF12/31/201412/31/2014194 
3F012DC0F325EF12/31/201412/31/2014220 
4F012DC0F325EF12/31/201412/31/2014230 
5F012DC0F325EF12/31/201412/31/2014230 
6F012DC0F325EF12/31/201412/31/2014235 
7F012DC0F325EF12/31/201412/31/2014250

 

and when i run select ,  i see this 

1F012DC0F325EF12/31/201404/28/2015240
2F012DC0F325EF12/31/201404/08/2015289
3F012DC0F325EF12/31/201403/16/2015290
4F012DC0F325EF12/31/201403/25/2015289
5F012DC0F325EF12/31/201401/21/2015238
6F012DC0F325EF12/31/201403/23/2015180

 

do you know why this is happening ?

 

ballardw
Super User

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;

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
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
  • 6 replies
  • 943 views
  • 2 likes
  • 5 in conversation