@Andrea_Peng: Apart from the fact that you may have a problem with single quotes rather than double quotes in varnum(dsid,'_&i.'), the approach is not the right one - what you are doing is to generate code for all variables, whether they exist or not. If you want generate code only for the variables that actually exist, you should open the dataset ID in the macro, not in the datastep, something like: %macro test(); %local dsid rc i; %let dsid=%sysfunc(open(test_desc5)); data test; set test_desc5 ; drop _name_ _label_; %do i=25 %to 1500 %by 25; %if %sysfunc(varnum(&dsid,_&i.))^=0 %then %do; /* Code to execute if the variable _&i exists: */ if _&i = '' then /* something wrong here, what are you trying to do?? */ _&i = ''; if A&i = '' then A&i = .; %end; %end; %let rc=%sysfunc(close(&dsid)); run; %mend; ... View more

You have two possibilities: Either you change the variable to a valid SAS version 7 (or earlier) name, or you use option VALIDVARNAME=ANY and use a quoted string for your data set name. The second solution may be easier, as you can use the same macro variable for the WHERE clause and the data set name: data "&&subj&i"n; set have; where deptdescription="&&subj&i"; So your datastep will now create a single table, called "Public Health", instead of two tables, one called "Public" and the other called "Health". But you will then have to refer to it as "Public Health"n. ... View more

One possibility is to use PRX (Pearl Regular Expressions). It looks like you always have first the price, then the volume, separated by a comma:   Data want; set have; prxData=prxParse('/price=([0-9\.]+),volume=([0-9\.]+)/i'); array prices(*) 8 price1-price10; array volumes(*) 8 volume1-volume10; start=1; do _N_=1 to 10; call prxNext(prxData,start,-1,transactions,pos,len); if len=0 then leave; prices(_N_)=input(prxposn(prxData,1,transactions),best32.); volumes(_N_)=input(prxposn(prxData,2,transactions),best32.); end; drop start pos len; run; A short explanation of the string in PRXPARSE: it first looks for "price=". The string that follows is put in a paranthesis, so that it is stored in a capture buffer, the contents of the buffer must be digits or decimal point. Then follows the text ",volume=" and a new capture buffer with a number. The final "i" tells the function not to care about upper/lower case.   The PRXNEXT call routine automatically updates the START variable, so that the next call will look for the next occurence of the pattern. When no more patterns are found, the POS variable (position of pattern) is set to 0 when no more occurrences are found. The PRXPOSN function retrieves the content of the capture buffers. ... View more

How many drugs have you got? If not too many, you can use a binary variable to mark each combination, e.g. data have; infile cards truncover; input id $4. line1_regimen $char80.; cards; 0013 adali certoli hcq 001a certoli hcq etaner tofa goli 0034 adali inflix lef mtx 0054 adali hcq inflix mtx ;run; data drugs; set have; length drug $10; do _N_=1 to countw(line1_regimen,' '); drug=scan(line1_regimen,_N_,' '); output; end; keep drug; run; proc sql noprint; /* put quoted drug names in macro variable */ select distinct quote(strip(drug),'''') into :drugs separated by ' ' from drugs; %let noof_drugs=&sqlobs;   /* no. of drugs */ quit; data simple complex; set have; length comb $&noof_drugs; comb=repeat('0',&noof_drugs-1); array drugs(&noof_drugs) $10 _temporary_ (&drugs); do _N_=1 to countw(line1_regimen,' '); substr(comb,whichc(scan(line1_regimen,_N_,' '),of drugs(*)),1)='1'; end; if length(compress(comb,'0'))>2 then output complex; else output simple; run; COMB will then mark which unique drugs are present in each regimen. ('1' in position 1 means that drug no. 1 is present, '0' means that it is not, etc.) ... View more

I think you should check the box "Use prompt value throughout project" in the general prompt ialog. That way the macro variable does not get deleted after the process is run. ... View more

Instead of retaining some variables, setting them missing at first.ID, and having an if last.ID code block, I would prefer putting things in a "doW" loop (named after Ian Whitlock, who has done a lot to popularize it). And all the if-then-else code on a single variable fits naturally in a SELECT statement. Finally, you should take more care about how you format your code. Using line breaks and indents help a lot, and I think the code becomes a lot more readable if you do NOT capitalize all keywords - there are actually scientific studies showing that most people have an easier time reading lowcase text than capitalized text! So, if I were to take over your code from here, it would very soon look something like this: data want; format Rec Meta 1. PriDate LastDate RecDate MetaDate DDMMYYP10.;; do until(last.ID); set have; by ID; select(CaseType); when('Primary') PriDate = VisitDate; when('Recurrent') do; Rec=1; RecDate = min(RecDate, VisitDate); end; when('Metastasis') do; Meta=1; MetaDate=min(MetaDate, VisitDate); end; when('Rec/Meta') do; Rec=1; RecDate = min(RecDate, VisitDate); Meta=1; MetaDate=min(MetaDate, VisitDate); end; otherwise; end; end; LastDate= Visitdate; if Rec ne 1 then do; Rec=0; RecDate=VisitDate; end; if Meta ne 1 then do; Meta=0; MetaDate=VisitDate; end; drop VisitDate VisitNr CaseType; run; ... View more

One possibility is to use hash tables, you basically make an ordered hash table for each variable, putting your values in until the number you want is reached, and then repeatedly deleting the lowest (first) value.   To keep track of the hash tables you create another hash table(TREE) , with the variable name (VAR) as key.   Here is an example which finds the 3 highest values of A and B in you example data: %let n=3; /* rank that we want */ %let vars='A','B'; /* variables that we want */ %let id='Desc','ID'; /* id variable(s) */ data want; set have end=done; if _N_=1 then do; declare hash h; declare hiter i; length var $32 value $50; retain order 'A'; /* Ascending/Descending, A means highest values, D lowest */ declare hash tree(); tree.definekey('var'); tree.definedata('h','i'); tree.definedone(); do var=&vars; h=_new_ hash(multidata: 'Y', ordered: order); h.definekey(var); h.definedata(var,&id); h.definedone(); i=_new_ hiter('h'); rc=tree.add(); end; end; do var=&vars; rc=tree.find(); rc=h.add(); if h.num_items>&n then do; rc=i.first(); /* get item to delete */ rc=i.prev(); /* release iterator so that we can remove item */ rc=h.remove(); end; if done then do; rc=i.last(); /* get highest/lowest item */ do rank=1 by 1 until(i.prev()); value=vvaluex(var); /* get formatted value */ output; end; end; end; keep desc id var value order rank; run;  I use the VVALUEX function to return formatted values. That way you can have the character representations of different variable types (e.g. character, money, integer, date) in the same character variable. If you need to find lowest values, change the ORDER variable to D. ... View more

You have two problems. The first is that the statements in CALL EXECUTE() are not executed before after the datastep. But I assume that you have already changed your code to DOSUBL(), as others have suggested that.   The other problem is that the whole datastep is compiled before it is executed, meaning that the macro variable is parsed before the datastep is executed. To get the value assigned by the query, you should use SYMGET to get it: data _null_; rc=dosubl('proc sql;select distinct status into :dps from depst;quit;'); if symget('DPS') = 'P' then do; But this is probably not the easiest way to get the values. It is probably easier to create the list of jobs to be run in a new table: proc sql; create table jobs_to_run as select check.jobname, check.val, master.dependentjobname, check2.runstatus from check join master on check.jobname=master.jobname join check check2 on master.dependentjobname=check2.jobname ; quit; You can now just set the table and submit the batchjob if runstatus='P' and val='1'. Of course, you may have to check if you have already submitted the job once: proc sort data=jobs_to_run; by jobname; run; data _null_; job_already_run=0; do until(last.jobname); set jobs_to_run; by jobname; if job_already_run then continue; if val='1' and runstatus='P' then do; /* submit batch job */ job_already_run=1; end; run; ... View more