Proc SQL to create a macro

[LEINAARE]

Hello,

I have a dataset with three variables and approximately 400 observations.  One of the variables 'Diagnosis' is a character variable containing diagnostic codes of interest.  I am trying to use PROC SQL to create a macro variable that will list all the values of 'diagnosis' separated by quotation marks and commas (',').  Ultimately, I would like the macro to generate code that would be structured like this:

'code1','code2','code3','code4','code5','code6' .... 'code400'

I intend to use it with print procedures such as this:

proc print data=medical;
    where diagnosis in (&diagnosis);
    var unique_id diagnosis desc;
run;

The code I developed to create a macro called 'diagnosis' from values of the diagnosis variable is this:

proc sql noprint;
     select diagnosis
          into :diagnosis separated by "','"
     from work.diagnosis;
quit;

 It seems to have generated a list of values from each observation separated by ',' for each.  However, I keep running into errors.  I thought perhaps it was because the first and last values would not be captured with leading and training commas, so I wrote that into my print procedure as so:

proc print data=medical_claims;
     where diag in ('&diagnosis');
     var unique_id diag;
run;

Unfortunately, it is not running.  Could anyone provide some insight as to why this would not produce a string of character values captured within quotation marks and commas?

Thank you,

Ted

[novinosrin · Accepted solution]

Try:

proc sql ;
     select quote(strip(diagnosis))
          into :diagnosis separated by ', '
     from work.diagnosis;
quit;

 

proc print data=medical_claims;
     where diag in (&diagnosis);
     var unique_id diag;
run;

[Reeza]

Make sure there's a space between the comma otherwise you get that lovely message of something changing in the future.

[novinosrin]

Are you still getting that ?:) Or is it we have the best performing SAS software at DePaul labs. Hmm but great diligence indeed. Point taken

7746 proc sql ;
7747 select quote(strip(name))
7748 into :name separated by ','
7749 from sashelp.class
7750 where name in ('Alfred','Mary');
7751 quit;
NOTE: PROCEDURE SQL used (Total process time):
real time 0.04 seconds
cpu time 0.03 seconds

7756 proc print data=w noobs;
7757 where name in (&name);
7758 run;

NOTE: There were 2 observations read from the data set WORK.W.
WHERE name in ('Alfred', 'Mary');
NOTE: PROCEDURE PRINT used (Total process time):
real time 0.04 seconds
cpu time 0.00 seconds

[Reeza]

Oh, you're right! It's gone now.

[LEINAARE]

Hi @novinosrin,

Thank you for your suggestion.  Using the quote(strip()) functions worked great.  I have not seen that before.  Also, sorry for the delayed response.  I left my office shortly after posting the question yesterday.

Thanks,

Ted

[novinosrin]

Good morning @LEINAARE You're very welcome. Thank you posting questions. SAS is a video game for me 🙂

[PaigeMiller]

Just to provide a little clarity on the terminology, which may help you in future discussions or reading — you are not creating a macro. You are assigning values to a macro variable.

[LEINAARE]

Thanks for the clarification.  I am just beginning to explore proc sql.  So that is a helpful distinction.

[RW9]

Why go through the issue of creating a messy set of coding with macro variables, and quoting and all that malarky, a simple change would remove all that:

proc sql;
  create table med_claims as
  select *
  from   medical_claims
  where diag in (select diagnosis from work.diagnosis);
quit;

proc print data=med_claims;
  var...;
run;

[LEINAARE]

Hi @RW9,

Thanks responding with the proc sql modification.  I am new to proc sql (I actually watched my first training video last week).  Am I correct to interpret that the will subset an output dataset called "med_claims" that contains only observations with values contained in the "diagnosis" variable from the work.diagnosis dataset?

Thanks,

Ted

[RW9]

Yes, that is correct.  It is using whats called a sub-query, so whatever data is resolved by the inside query is then returned to the outer queries where clause.

[LEINAARE]

Thank you @RW9.  This will be very useful in the future!

[LEINAARE]

Hello All,

Thank you again for the great suggestions yesterday.  I have one more question regarding this code.  I realized later that using the "into" statement creates a local macro.  For my purposes, I need macros created from this proc sql to be global. 

I created a macro to run the proc sql code three times (shown below).  Each time, I want to create a different global macro variable to use throughout other sections of code.

%macro makemacro(var,macro);
	%global macro;
	%let macro=&macro;
	proc sql noprint;
		select quote(strip(&var))
			into :&macro separated by ','
		from &var;
	%put &macro;
	quit;
%mend makemacro;

%makemacro(ndc,ndcmac);
%makemacro(procedure,procmac);
%makemacro(diagnosis,diagmac);

I did some research on my own, and found the %global option.  However, I get the following error message.

1127  %makemacro(ndc,ndcmac);
ERROR: Attempt to %GLOBAL a name (MACRO) which exists in a local environment.
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds

Can anyone offer a suggestion to correct this?  I have experimented around, but cannot find anything to help resolve it.

Thanks,

Ted

[novinosrin]

Try assigning different name in the assignment as opposed to

%let macro=&macro;

something like

%let macro1=&macro;