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
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;
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;
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
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
Good morning @LEINAARE You're very welcome. Thank you posting questions. SAS is a video game for me 🙂
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.
Thanks for the clarification. I am just beginning to explore proc sql. So that is a helpful distinction.
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;
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
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.
Thank you @RW9. This will be very useful in the future!
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=¯o;
proc sql noprint;
select quote(strip(&var))
into :¯o separated by ','
from &var;
%put ¯o;
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
Try assigning different name in the assignment as opposed to
%let macro=¯o;
something like
%let macro1=¯o;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.
Find more tutorials on the SAS Users YouTube channel.