You can also try this: data want; set have; by id; if first.id then call missing(want_lag1_income,want_lag2_income); output; want_lag2_income=want_lag1_income; want_lag1_income=income; retain want_lag1_income want_lag2_income; run; ... View more

How can I know if my task was successfully export ? Take look at the file you created, and see if it looks like you expected (open it in a text editor). If you can, test the output in whatever program is supposed to receive it. ... View more

There are two differences between implicit and explicit pass-through: Using explicit pass-through, you have to use the local SQL dialect instead of SAS SQL. So you may have to change the code quite a bit from what you are used to. On the other hand a lot of the other SQL dialects have some nifty constructs, which you may not have in SAS. When doing SAS SQL on libnames that refer to external databases, it only becomes pass-through if you are careful not to use SAS functions that are not representable in the SQL dialect of the external database. I think you can see the documentation of which functions are supported in the SAS/ACCESS documentation for the various databases. Does that answer your question? ... View more

There are some ways in which you can make your query more efficient: Instead of "select *", write a list of the columns that you actually need. This may take some time, but it will also make the code a lot easier to read and maintain, and it will also save memory for the output. You are not calculating any summarized columns, so I do not see why you have the "group by". This is probably an unnecessary waste of CPU and memory.   ... View more

I tried your code in SAS Studio, and did not get any errors.   I am not sure, but one explanation that you get this error could be that PROC HTTP tries to run several threads - if that's the case, you could try using option nothreads; in your macro before calling PROC HTTP. ... View more

If you are actually looking for records where the variable STARTS with 't_hier_testing_diab_cv1', you can probably get SAS to use the index for searching by using LIKE instead of the INDEX() function: data j__; set adhc.codetrak (where=(program_name like 't_hier_testing_diab_cv1%')); run; But as you are also using the LOWCASE() function, that does not seem to be a possible solution. ... View more

Given data like this data have; format id 8. date date9.; informat date date.; input id date; cards; 1 01jan21 1 10jan21 2 5feb21 2 5feb21 ;run; you can use code like this data want; set have; by id; datediff=dif(date); if first.id the datediff=.; run; The code will work if your dates are SAS date values, as shown in the example.   - although I am not quite sure why your example data show the difference to the next record for the first record in each group.   If that is really what you want, it can be done like this: data want; set want; by id; if first.id and not last.id then do; _N_=_N_+1; set want(keep=datediff) point=_N_; end; run;   ... View more

Are you actually trying to get the admission and discharge dates by year, so that you have an observation for each year with a valid period? Then something like this may work: data want; set have; do year=2018 to 2023; date1=mdy(1,1,year); if .<dsch_dt<date1 then leave; /* no more output for this observation */ date2=mdy(12,31,year); if admit_dt>date2 then continue /* no output for this year */ date1=max(date1,admit_dt); date2=min(dsch_dt,date2); output; end; run; ... View more

I assume that your data is sorted by ID and TIME, and if you do not sort the data by ID and TYPE before the data step, the solution proposed by @whymath should work (note the NOTSORTED option on the BY statement) - or this one, which makes the count in the loop header: data want; do _N_=1 by 1 until(last.type); set have; by id type notsorted; end; if _N_>2 then flag ='*'; do until(last.type); set have; by id type notsorted; output; end; run; ... View more

I would not use PRX for that, just SCAN: data want; set have; reason=scan(scan(value,2,' '),1,':,.'); if countw(value,' ')>2 then ReasonSp=scan(scan(value,-1,' '),1,':,.'); run; The reason the ReasonSp calculation is a bit more complicated is to account for the "i.e." in the second observation, otherwise it could be calculated by taking the third word - but instead we have go get the last word, but only if there are more than 2 words. ... View more

There seems to be several problems with your code: As @Tom already wrote, you must get the Oracle date strings into single quotes You swapped the PUBLISH_DT and the PUBLISH_YR_MNTH variables in the MONTHS table, and your input will not work (at least the second variable should be character) - I assume you have a MONTHS table from elsewhere, and did not test the code to create it as shown. The code will overwrite the same output table (WORK.BK_SAMP) repeatedly Instead of using a macro, I would consider writing the program to a temporary SAS file, e.g. data months; input publish_yr_mnth 7. publishdt $11.; datalines ; 201001 01-JAN-2010 201002 02-FEB-2010 201005 02-MAY-2010 ;run; filename tempsas temp; data _null_; set months; file tempsas; put 'proc sql ; ' / ' connect to oracle as mydb(user=user orapw="&dbpass" path=oracledb );' / ' create table bk_samp' _N_ ' as select * from connection to mdb ( '/ ' select author ,title from books_db where publish_dt >=''' publishdt +(-1) ''' and publish_yr_mnth=' publish_yr_mnth / ' ) ; '/ 'quit;'; ; %include tempsas / source2; To test the code, you can start by commenting out the "file tempsas" line and the "%include" line, and just look at the generated code in the log until it's OK, then uncomment and run for real.   I put in the _N_ after "BK_SAMP" so that 3 tables are created (BK_SAMP1-3). You may want to use the PUBLIST_YR_MNTH as a suffix instead, or collect all the data in a single table (remember to select the PUBLIST_YR_MNTH column also in that case). ... View more

I would parse the data with a regular expression: data have; string='999999999K 20APR23:11:39:00 AM'; run; data want; prxid=prxparse('/ ([^:]*):(.*)$/'); set have; if prxmatch(prxid,string); period=input(prxposn(prxid,1,string),date.); dts=dhms(period,0,0,input(prxposn(prxid,2,string),time.)); drop prxid; format period ddmmyy10. dts datetime23.; run; The PRX expression looks for a blank, followed by something that is not a colon (the first capture buffer), followed by a colon (not captured), and then the rest of the string ("$" means end of string) in the second capture buffer. Once you have the parts its a piece of cake - I put the time value in the seconds parameter of DHMS(), as a SAS time value is actually the number of seconds since midnight, instead of using 3 function calls to get the hours, minutes and seconds.   Of course, if your data is in a flat file, and not in SAS data, use INPUT (replace the CARDS with the actual physical infile): data want; input somestring : $20. period date7. +1 time time11.; format period ddmmyy10.; dts=dhms(period,0,0,time); format dts datetime23.; cards; 999999999K 20APR23:11:39:00 AM ;run; ... View more

Here is a datastep solution: data want; set have end=done; array vars(*) _numeric_; array changes(100) 8 _temporary_; do _N_= 1 to dim(vars); changes(_N_)+vars(_N_) ne lag(vars(_N_)); end; if done; do _N_= 1 to dim(vars); if changes(_N_)=1 then do; vname=vname(vars(_N_)); output; end; end; keep vname; run; ... View more

I think you should drop the leading and trailing blanks, and look for word boundaries instead - beginning of string or end of string is also a word boundary: pattern_id=prxparse('/\b(DONEPEZIL|GALANTAMINE|RIVASTIGMINE|MEMANTINE)\b/i'); ... View more

I assume the by "LSF" you mean either the IBM Load Sharing Facility, or the similar named facility from Platfor Computing (or maybe the two are the same?). I am not sure, but I think that what happens is that when querying in LSF, some records are not read, possibly because they are locked by other users, as the result from the first run is always smaller than the result from the second run.   What to do about it? Sorry, no idea. ... View more