topic Re: Proc SQL to create a macro in New SAS User

Proc SQL to create a macro

LEINAARE — Mon, 17 Dec 2018 23:13:04 GMT

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

Re: Proc SQL to create a macro

novinosrin — Mon, 17 Dec 2018 23:31:17 GMT

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;

Re: Proc SQL to create a macro

Reeza — Mon, 17 Dec 2018 23:27:50 GMT

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

Re: Proc SQL to create a macro

novinosrin — Mon, 17 Dec 2018 23:31:01 GMT

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

Re: Proc SQL to create a macro

Reeza — Mon, 17 Dec 2018 23:44:15 GMT

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

Re: Proc SQL to create a macro

PaigeMiller — Tue, 18 Dec 2018 00:12:43 GMT

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.

Re: Proc SQL to create a macro

RW9 — Tue, 18 Dec 2018 11:46:50 GMT

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;

Re: Proc SQL to create a macro

LEINAARE — Tue, 18 Dec 2018 13:21:25 GMT

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

Re: Proc SQL to create a macro

LEINAARE — Tue, 18 Dec 2018 13:25:19 GMT

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

Re: Proc SQL to create a macro

LEINAARE — Tue, 18 Dec 2018 13:45:18 GMT

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

Re: Proc SQL to create a macro

RW9 — Tue, 18 Dec 2018 13:47:41 GMT

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.

Re: Proc SQL to create a macro

LEINAARE — Tue, 18 Dec 2018 14:05:07 GMT

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

Re: Proc SQL to create a macro

novinosrin — Tue, 18 Dec 2018 14:17:37 GMT

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

Re: Proc SQL to create a macro

LEINAARE — Tue, 18 Dec 2018 22:48:20 GMT

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

Re: Proc SQL to create a macro

novinosrin — Tue, 18 Dec 2018 22:55:27 GMT

Try assigning different name in the assignment as opposed to

%let macro=&macro;

something like

%let macro1=&macro;

Re: Proc SQL to create a macro

RW9 — Wed, 19 Dec 2018 09:22:08 GMT

You are just making things worse for yourself. Creating global variables can cause system wide bugs which are very hard to track down. I would really advise you to re-think your process.

Re: Proc SQL to create a macro

Reeza — Wed, 19 Dec 2018 16:05:33 GMT

I would really really recommend not naming your macro variables or macros's macros. You'll get weird behaviour that you're not expecting and it's just a recipe for debugging hell.

Re: Proc SQL to create a macro

LEINAARE — Wed, 19 Dec 2018 22:07:46 GMT

Hi @RW9,

Thanks for the advice. I found a way to perform my desired procedures using local macros within the program.

Re: Proc SQL to create a macro

LEINAARE — Wed, 19 Dec 2018 22:08:33 GMT

Hi @Reeza,

Thanks for the suggestion. That makes sense going forward!