Obviously, the value of the DIAG1 variable is not always between 1 and 6496. Is it missing or 0 in some observations? What does the log say about the value of DIAG1 when the error occurs? ... View more

If you want the data in the order shown (as in the B table), you cannot use SQL the way some have suggested. The most obvious solutions seem to use a hash or an index; the index solution is probably the simplest to program: data A;   input id x y; cards; 1 12 34 2 23 24 3 54 56 4 85 45 5 98 92 ;run; data B;   input id; cards; 1 2 2 1 3 2 1 4 5 ;run; proc sql;   create index id on a(id); quit; data want;   set b;   set a key=id/unique;   if not _iorc_; run;   The "if not _iorc_" line says that you do not want any output if the ID is not found in A. If you do want the data from B anyway, you should set X and Y missing to show that no data were found in A: if _iorc_ then do;   call missing(x,y);   _error_=0;   end; I put the _error_=0 in because in that case, you probably do not want an error message when there is no data in A. ... View more

Here is a way to do it. Not sure how well it scales. If you have large amounts of data, a data step solution may be faster: data have; input customer_ID $/date date9./NoOfItems; format date date9.; cards; 2333A 24-May-07 3 2333A 27-Jun-07 5 2333A 8-Jul-07 3 2333A 9-Jul-07 8 2333A 20-Jul-07 9 2333A 25-Jun-07 9 2333A 30-Jun-07 7 2333A 5-Jul-07 6 2333A 7-Jul-07 1 5643D 24-May-07 5 5643D 27-Jun-07 5 5643D 8-Jul-07 5 5643D 9-Jul-07 5 5643D 20-Jul-07 5 5643D 25-Jun-07 6 5643D 30-Jun-07 7 5643D 5-Jul-07 5 5643D 7-Jul-07 1 5643D 18-May-07 2 5643D 20-Jun-07 2 2287C 25-Jun-07 4 2287C 30-Jun-07 2 2287C 5-Jul-07 3 ;run; proc sql; create table Sixty_sum as select Customer_id,date,(select sum(NoOfItems) from have where Customer_id=outer.Customer_ID and date between outer.Date and outer.Date+59) as NoOfItems60 from have as outer having calculated NoOfItems60>40 ; ... View more

One way to speed up things may be to do the reading and the interleaving in one step. In other words, instead of reading the files first, create the input steps as data step views, and then set the views: data v1/view=v1; infile file1 dsd delimiter=';'; input... /* etc. */ data v2/view=v2; infile file2 dsd delimiter=';'; input... /* etc */ data interleaved; set v1 v2 v3 v4 v5 v6...; by num_key; run; ... View more

SAS does not have the concept of primary keys. But you can add a unique index, which amounts to about the same: proc sql; create unique index prim_key on aaa(ID,month); quit; The only difference being that unique keys do not require the columns to be NOT NULL. You will have to add constraints for that separately, if you want it. ... View more

Rather than hardcoding "height" and weight" I would use transpose to get the final table: proc sql;   create table last as   select vsone.*   from vsone join exone     on vsone.id=exone.id and vsone.vdate <= exone.edate   where value ne .   group by vsone.id,tcd   having vsone.vdate=max(vsone.vdate); quit;   proc transpose out=want(drop=_name_) suffix=tbl;   by id;   var value;   id tcd; run; I would have used the code window to post code, but for some reason "paste" does not work right now (just got a new Windows version, maybe therefore).  Rather than convering all the date texts to SAS dates, you can use the fact that they are ordered (both formats start with yyyy-mm-dd). I dropped the aliases, they do save a few keystrokes, but otherwise they just make the program harder to read. I also dropped the left join. But maybe you want the highest VDATE when there is no EDATE? Then use the left join again, and add "or exone.edate is null" to the where clause. ... View more

To put an error in the log, use the log metod, e.g.: proc ds2 ; data _NULL_; dcl package logger root(); method init(); root.log(n'error', 'you got yhis all wrong!'); end; enddata; run; quit; To stop execution, use STOP or whatever. ... View more

If you have SAS/Graph, you can convert to degrees with PROC GPROJECT. ... View more

According to the documentation, no. UTM coordinates are not an option. Coordinates can be degrees or radians. ... View more

I understand that you want both the number and the unit. The something like this may work: data _null_; text='.25 Mg Every 3 Hours Prn'; prxid=prxparse('/([0-9\.]+)\s+(Meq|mg|ml|gram(\w)*|gm|mcg)/i'); if prxmatch(prxid,text) then do; number=prxposn(prxid,1,text); unit=prxposn(prxid,2,text); end; put _all_; run; The PRXPOSN function returns the string inside a capture buffer (the stuff inside the parantheses). ... View more

I think that your problem is the coordinates. The numbers should be in degrees, between 0 and 360, what you have is much too high. Maybe you have the wrong unit. I assume that your longitudes and latitudes are 10,000 times too big, but it could be something else.   distance2=geodist(726021,6272193,726031,6272193)*1000; gives a missing value, distance2=geodist(72.6021,62.72193,72.6031,62.72193)*1000; gives a distance of 111 meters and 59 centimeters. Is that what you expected?   ... View more

I try to avoid returning values in global macro variables when I can. In this case it is not hard. First, here is a macro to get a variable list from a table (with a little helper macro first): /* Execute a function, check SYSRC() and possibly write SYSMSG() */ %macro chkfunc(func); %*;%sysfunc(&func) %if %sysfunc(sysrc()) %then %put %qsysfunc(sysmsg()); %mend; /* Create a variable list from KEEP and DROP specifications */ /* NB: If both are used, the intersection of the two will be returned */ %macro varlist(data,keep=,drop=,separator=%str( )); %local ds dskeep dsdrop rc varnum nvars varname sep; %let ds=%chkfunc(open(&data)); %if &ds=0 %then %return; %let dskeep=%chkfunc(open(&data(keep=&keep))); %if &dskeep=0 %then %goto errorkeep; %let dsdrop=%chkfunc(open(&data(drop=&drop))); %if &dsdrop=0 %then %goto errordrop; %do varnum=1 %to %chkfunc(attrn(&ds,nvars)); %let varname=%chkfunc(varname(&ds,&varnum)); /* No %CHKFUNC in the following, we do not want error */ /* messages when variables not found */ %if %sysfunc(varnum(&dskeep,&varname)) and %sysfunc(varnum(&dsdrop,&varname)) %then %do; %*;&sep.&varname %let sep=&separator; %end; %end; %let rc=%chkfunc(close(&dsdrop)); %errordrop: %let rc=%chkfunc(close(&dskeep)); %errorkeep: %let rc=%chkfunc(close(&ds)); %mend; Now you can get a list of variables from a dataset in a simple function-call style macro, e.g. %let a=%varlist(sashelp.class,drop=_character_); This can easily be used to get variables starting with some string (use ":" as a wild-card in the keep parameter). To get the names ending with something, here is another macro, which checks a list against a PRX expression, and returns the items matching: /* Filter list of words using PRXMATCH */ %macro prxfilter(prx,str,delim=%str( ),separator=%str( ),options=); %local i w prxid scanq quote scanf sysf sep; %let prx=%superq(prx); %let options=%upcase(&options); /* option Quote means that the output will be quoted */ %let quote=%sysfunc(indexw(&options,QUOTE)); %if &Quote %then sysf=qsysfunc; %else %let sysf=sysfunc; /* Options SCANQ means that the SCANQ function will be used */ %let scanq=%sysfunc(indexw(&options,SCANQ)); %if &scanq %then %let scanf=SCANQ; %else %let scanf=SCAN; %if %sysfunc(verify(&prx,1234567890))=0 %then %let prxid=&prx; /* The prx parameter is a PRX ID */ %else %let prxid=%sysfunc(prxparse(&prx)); %if &prxid=0 %then %return; /* COUNTW is only available from 9.2, hence this "primitive" approach */ %do i=1 %to 99999; %let w=%&sysf(&scanf(&str,&i,&delim)); %if %length(&w)=0 %then %do; %if &prx ne &prxid %then /* we allocated a local PRX ID, free it */ %syscall prxfree(prxid); %return; %end; %if %sysfunc(prxmatch(&prxid,&w)) %then %do; %*;&sep.&w %let sep=&separator; %end; %end; %mend; So, you can now get your bats like this: %put %prxfilter(/bat$/i,%varlist(sashelp.baseball)); The nice thing about this approach is that you can use the macro functions whereever you like, e.g. proc sql; select distinct %prxfilter(/bat$/i,%varlist(sashelp.baseball),separator=%str(,)) from sashelp.baseball; The problem with using global macro variables to return values are two. One is that they sometimes collide, meaning that one macro overwrites the global variables of another. The other is that sometimes you have macros call other macros, and so on. If your macro is really considered useful, there is great chance that it will be embedded in another macro. And then you will call that macro from a third macro. And your new macro will have a local variable called e.g.SUFFIX, and you will get the message "Attempt to globalize a variable already defined as local", and you macro will not run.   This is why, when I have to put something in a macro variable in order to return a value, I often use a "SEMIGLOBAL" construct. It is very simple: If a macro variable is not already defined in the calling environment, define is as global. Otherwise, do nothing. Like this: %macro semiglobal/parmbuff; %let syspbuff=%scan(&syspbuff,1,( ,)); /* only one name will be used */ %if not %symexist(&syspbuff) %then %global &syspbuff; %mend; So,l now you can have your macros return values in global or local variables. So if you have a macro that returns something in a macro variable, declare that macro variable Semi-global: %macro x; %semiglobal(rvalue); /* return value */ /* more stuff here */ %mend; Another macro calling the X macro can then make the RVALUE variable local without any problems (actually, avoiding possible problems in the future). ... View more