Hello,   I am using the SQL procedure to create macros as written below.  When I write the code below, macros are created as desired and work as global macros outside the SQL procedure. proc sql noprint; select quote(strip(diagnosis)) into :PregnanacyDiag separated by ',' from PregnancyDiag; quit; %put &PregnanacyDiag; /*appropriate code is written to the log*/ I am using the above procedure many times to create macro variables containing quoted strings of code from numerous datasets which contain procedural, diagnostic, and drug codes.  Accordingly, it makes sense to convert this code into a macro.  I attempted to do so as show below. %macro makemac(var,macname,dataset); select quote(strip(&var)) into :&macname separated by ',' from &dataset; %mend; proc sql noprint; /*Diaphragm Lookup Codes*/ %makemac(procedure,ProcMAC_Diaphragm,DiaphragmProc); %makemac(ndc,NdcMAC_Diaphragm,DiaphramNDC); /*Implant Lookup Codes*/ %makemac(diagnosis,DiagMAC_Implant,ImplantDiag); %makemac(procedure,ProcMAC_Implant, ImplantProc); %makemac(ndc,NdcMAC_Implant,ImplantNDC); quit; %put &ProcMAC_Diaphragm; I expected that the SQL procedure would run as normal with the '%makemac' macro inserting appropriate syntax for select statements for each line of code.  When I run the program, the log appears normal.  I used the 'symbolgen' option, and it shows the appropriate values replaced for each line of macro code.  But when I try to use the '%put &(macro name)' statement, it does not write the string of quoted code to the log I expected, and I receive instead the warning below.   "WARNING: Apparent symbolic reference PROCMAC_DIAPHRAGM not resolved."   If anyone could offer suggestions to fix this, I would greatly appreciate it.  I am using this code many times.  When I hard code it, it works as desired.  But streamlining the code as a macro makes more sense to me.  Also, I'd just like to understand the error.   Thanks,   Ted ... View more

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 ... View more

Hello,   I have a dataset that contains numerous observations for each unique id (i.e. 'orsid').  I am preparing my data to run a program that selects Medicaid claims data specific to family planning.  In order for eligible enrollees who do not have claims data in a given year to be included in the denominator for calculations, I am supposed to retain one observation for that individual, and set the date to January 1 of the year.     I know that my use of 'first.orsid' in the code below is incorrect, but I am including it to demonstrate the logic that I have been trying to use.  Could anyone help me to devise a strategy for retaining the first observation and changing the date only for individuals who do not have claims records for that year?  If an individual (orsid) has no claims data for the year, then they would have 12 observations for that year (Jan 01 - Dec 01) in the input dataset.  If they do have claims data, they would have many more observations than that.   data claim12b; set claim12; by orsid; if (icd1='' and icd2='' and icd3='' and icd4='' and icd5='') and (prc1='' and prc2='' and prc3='') and (ndc1='' and ndc2='' and ndc3='' and ndc4='' and ndc5='' and ndc6='') then do; keep first.orsid; date="o1jan2012"d; end; run; I know this is incorrect in two ways.  First, even if the logic worked, it would read each individual observation, rather than reading all 12 observations for the unique ID and determining if the individual had no claims during the course of 12 months.  Secord, the 'keep first.orsid' statement does not work.   I greatly appreciate any guidance you may offer.   Thank you,   Ted ... View more