About ScottBass

ScottBass · ‎11-25-2011

Hi, I need to get the distinct values of formatted date periods for further processing in a macro. Here is some test code: data dates; start="01JAN2011"d; do i=0 to 23; monthStart=intnx("month",start,i,"B"); monthEnd=intnx("month",start,i,"E"); monyy=monthStart; yyqtr=monthStart; year=monthStart; output; end; keep monthStart monthEnd monyy yyqtr year; format monthStart monthEnd date9. monyy monyy. yyqtr yyq. year year.; run; proc sql noprint; select distinct put(year,year.) into :periods separated by " " from dates; %put period=&periods; select distinct put(yyqtr,yyq.) into :periods separated by " " from dates; %put period=&periods; * bogus error message ; select distinct put(monyy,monyy.) into :periods separated by " " from dates order by monthStart; %put period=&periods; * this works but is klunky ; select distinct monthStart, put(monyy,monyy.) into :dummy, :periods separated by " " from dates order by monthStart; %put period=&periods; quit; For the final query, I want the monyy. dates to be processed in chronological rather than alphabetical order. Is the test code above the best approach to avoid this bogus error message in SQL: WARNING: The query as specified involves ordering by an item that doesn't appear in its SELECT clause. Since you are ordering the output of a SELECT DISTINCT it may appear that some duplicates have not been eliminated. Is there any reason I should care that I'm ordering by an item that doesn't appear in its SELECT clause? I just want SAS to "do the right thing" and not issue warning statements unless it's a real issue. I found this Usage Note from 1993: http://support.sas.com/techsup/unotes/V6/6/6925.html. Little birdies: why would this issue, marked as High Priority, be fixed for some operating systems, but still be an issue 8 years later on Windows? Thanks, Scott

ScottBass · ‎10-26-2011

Hi, SAS 9.2 on Windows XP I'm trying to run this code: options ls=max; filename foo pipe " ""C:\Program Files\sysinternals.com\handle.exe"" -u ""%sysfunc(pathname(work))"" "; data _null_; infile foo; input; putlog _infile_; run; filename foo; I need to pass a quoted path to the program since it has a space in the path. I need to pass a quoted file path to handle because the tildes in the short path throw off the program. My actual program will use the %sysfunc(pathname(whatever)) function to get the pathname to an allocated library, which might contain a space or a tilde (if the short name is returned). I get this error: 1304 options ls=max; 1305 filename foo pipe " ""C:\Program Files\sysinternals.com\handle.exe"" -u ""%sysfunc(pathname(work))"" "; 1306 data _null_; 1307 infile foo; 1308 input; 1309 putlog _infile_; 1310 run; NOTE: The infile FOO is: Unnamed Pipe Access Device, PROCESS="C:\Program Files\sysinternals.com\handle.exe" -u "C:\DOCUME~1\sbass\LOCALS~1\Temp\SAS Temporary Files\_TD2556", RECFM=V,LRECL=256 Stderr output: 'C:\Program' is not recognized as an internal or external command, operable program or batch file. NOTE: 0 records were read from the infile FOO. NOTE: DATA statement used (Total process time): real time 0.06 seconds user cpu time 0.00 seconds system cpu time 0.00 seconds Memory 188k OS Memory 16240k Timestamp 26-Oct-11 18:10:31 If I cut and paste the text from PROCESS= into a cmd window it works fine: C:\>"C:\Program Files\sysinternals.com\handle.exe" -u "C:\DOCUME~1\sbass\LOCALS~1\Temp\SAS Temporary Files\_TD2556" Handle v3.41 Copyright (C) 1997-2008 Mark Russinovich Sysinternals - www.sysinternals.com sas.exe pid: 2556 INTERNAL\sbass 33C: C:\DOCUME~1\sbass\LOCALS~1\Temp\SAS Temporary Files\_TD2556\regstry.sas7bitm sas.exe pid: 2556 INTERNAL\sbass 40C: C:\DOCUME~1\sbass\LOCALS~1\Temp\SAS Temporary Files\_TD2556\_tf0001.sas7bitm sas.exe pid: 2556 INTERNAL\sbass 668: C:\DOCUME~1\sbass\LOCALS~1\Temp\SAS Temporary Files\_TD2556\sasmacr.sas7bcat sas.exe pid: 2556 INTERNAL\sbass 67C: C:\DOCUME~1\sbass\LOCALS~1\Temp\SAS Temporary Files\_TD2556\_tf0002.sas7butl sas.exe pid: 2556 INTERNAL\sbass 1090: C:\DOCUME~1\sbass\LOCALS~1\Temp\SAS Temporary Files\_TD2556\_tf0009.sas7butl sas.exe pid: 2556 INTERNAL\sbass 10C4: C:\DOCUME~1\sbass\LOCALS~1\Temp\SAS Temporary Files\_TD2556\_tf0019.sas7butl If I remove the path filter the command works fine from SAS. Any ideas how to fix this issue? Thanks, Scott

ScottBass · ‎10-07-2011

Hi Linus, Are you saying you disagree with the points raised in http://www.lexjansen.com/pharmasug/2005/posters/po33.pdf? Scott

ScottBass · ‎10-07-2011

Hi Fried, First of all, thanks a lot for your posts on this subject. Much appreciated. Second, I'm running on Windows, which has a default increment of 1 second for the SLEEP() function, whereas Unix has 1 millisecond. I posted this on SAS-L to get a wider audience, and have this working now. Rather than re-post that long message here, here is the link to my SAS-L posting: http://www.listserv.uga.edu/cgi-bin/wa?A2=ind1110a&L=sas-l&D=0&X=746AD217485B47084E&P=26500 Regards, Scott

ScottBass · ‎10-06-2011

Hi, According to the documentation (link from @Scott Bass): If the data set contains duplicate keys, by default, the first instance is stored in the hash object; subsequent instances are ignored. To store the last instancein the hash object, use the DUPLICATE argument tag. The DUPLICATE argumenttag also writes an error to the SAS log if there is a duplicate key. Actually, what I meant was, you need to understand the difference between: declare Hash H(dataset: "dates"); H.DefineKey ('number'); H.DefineData ('date'); H.DefineDone (); /* complete hash table definition */ And (untested): declare Hash H(); H.DefineKey ('number'); H.DefineData ('date'); do until (eof); set dates end=eof; H.Add(); end; H.DefineDone (); /* complete hash table definition */ And: declare Hash H(); H.DefineKey ('number'); H.DefineData ('date'); do until (eof); set dates end=eof; H.Replace(); end; H.DefineDone (); /* complete hash table definition */ Assuming a sorted order of your keys, I view Add() as "keep the first occurence" and Replace() as "keep the last occurence" of the key value. Somewhat analogous to first. and last. processing (just an analogy, not identical!) I didn't read the finer points of the original post, so this knowledge may or may not be relevant, but it's good to know regardless. I have sometimes used two hash objects, the first to "de-dup", or otherwise get my desired data into a hash, and the second hash object (using different keys) to return the data in desired sort order. Regards, Scott

ScottBass · ‎10-06-2011

Hi, Thanks for the reply... 1) The SPDE libname statement does not support the FILELOCKWAIT option: 422 libname spde spde "c:\temp" filelockwait=300; ------------ 22 ERROR: Libname SPDE is not assigned. ERROR: Error in the LIBNAME statement. ERROR 22-7: Invalid option name FILELOCKWAIT. 2) SPDE does not support the LOCK statement: 424 lock spde.test; ERROR: The FILE LOCKING function is not supported by the SASSPDE engine. 3) I tried to roll my own locking routine using the OPEN function, but unlike the SCL OPEN function, the port to base SAS does not support Update ("U") open mode. See http://support.sas.com/documentation/cdl/en/lrdict/64316/HTML/default/a000148395.htm vs. http://support.sas.com/documentation/cdl/en/sclref/59578/HTML/default/viewer.htm#a000143447.htm. I tried this in one SAS session: data spde.test; x=1; rc=sleep(60); run; And this in another: %macro lock(dataset); %let dsid=%sysfunc(open(&dataset,i)); %put dsid=&dsid; %let rc=%sysfunc(fetchobs(&dsid,1)); %put rc=&rc; %if (&dsid gt 0) %then %let dsid=%sysfunc(close(&dsid)); %end; %mend; %lock(spde.test); I also tried IN and IS mode, but I could never get the open function to fail and return dsid=0. Of course, this code in the 2nd SAS session fails: 425 data spde.test; 426 x=1; 427 run; ERROR: The file is already locked by another user. ERROR: Requested lock is not available for data set. NOTE: The SAS System stopped processing this step because of errors. By contrast, using the BASE engine, the below code works: Session #1: data base.test; x=1; rc=sleep(30); run; Session #2: %macro lock(member=,timeout=120,retry=2,action=LOCK,onfail=STOP); %local starttime; %let starttime = %sysfunc(datetime()); %if %UPCASE(&ACTION)=LOCK %then %do; lock &member; %if &SYSLCKRC > 0 %then %do %until(%sysevalf(%sysfunc(datetime()) > (&starttime + &timeout))); %let SLEPT=%sysfunc(sleep(&retry)); %put NOTE: Trying to lock %UPCASE(&MEMBER)...; lock &member; %if &syslckrc LE 0 %then %do; %return; %end; %end; %if &syslckrc > 0 %then %do; %if %upcase(&ONFAIL)=STOP %then %do; %abort cancel; %end; %if %upcase(&ONFAIL)=SYSCC %then %do; %let SYSCC=&SYSLCKRC; %return; %end; %end; %end; %if %UPCASE(&ACTION)=CLEAR %then %do; lock &member query; %if &SYSLCKRC = %sysrc(_SWLKYOU) %then %do; lock &member clear; %end; %end; %mend lock; %lock(member=base.test,action=lock); data base.test; do x=1 to 10; output; end; run; %lock(member=base.test,action=clear); Anyone know if there is a way to duplicate this functionality using SPDE? Thanks, Scott

ScottBass · ‎10-05-2011

Hi, yes, as I understand it: * BASE engine default locking level is record level locking, SPDE engine default locking level is member level locking * For a SPDE dataset: "Concurrent access from multiple SAS sessions on a given data set - READ (INPUT open mode)" (see p. 7 of the SPDE PDF) * So, any access (i.e. READ) to a SPDE dataset from another process will prevent any other process from writing to that dataset I've also implemented UNOTE 18467, i.e. spdedebug="cache_disable". However, there are times when you want to "manually" get an exclusive lock on a dataset. The aforementioned paper www.lexjansen.com/pharmasug/2005/posters/po33.pdf goes into all the details, so I won't repeat them here. What I'm finding from my testing of SPDE is: * All my current ETL jobs that implement locking now generate an error. Bummer that SAS didn't document this issue. A search of the 9.2 SPDE PDF on "lock" gets no hits. Little birdies, how about updating p. 7 of the SPDE doc? * The lock statement allows you to wait if you can't get an exclusive lock on a dataset. So, instead of immediately failing, you can wait to see if you can get the lock later. * Because I can't implement locking, I'm getting these errors in my ETL jobs: ERROR: The file is already locked by another user. ERROR: Member medprovstatusdim not replaced(DARENAME failed): Reason=Requested lock is not available for data set ERROR: The file is already locked by another user. The only thing that could be locking these SPDE datasets in our test environment is another test ETL job (in my current ETL stream). I'll investigate if there's a dependency issue with the scheduling that can be tweaked to fix this issue, but it would be nice if SAS would document all the differences between the BASE and SPDE engine to save customer's time. If there is a way to get an exclusive lock on a SPDE dataset, please let me know. Thanks, Scott

ScottBass · ‎10-04-2011

Hi, A few points: See http://support.sas.com/documentation/cdl/en/lrcon/62955/HTML/default/viewer.htm#a002585310.htm, in particular: 'NO' | 'N' Data is returned in an undefined order, for the ordered tag (NO is the default). Also, understand the difference between the ADD() and REPLACE() methods. You might want to load the hash object with do until (eof); set whatever end=eof; h.add() construct. You could use two hash objects and a hash iterator to load a hash object, let the first hash object do your desired processing, and the second hash object write your output dataset in desired sort order. You'd have to test if this gives you the perfornance you want. HTH... Scott

ScottBass · ‎10-04-2011

Hi, We have some existing ETL code which uses the lock statement to ensure another process doesn't get a lock on the target dataset and cause the ETL job to fail. We're investigating changing from the BASE to SPDE engine for certain libraries. The SPDE library doesn't like the lock statement and gives an error message, although I can't find where that is documented. Does the SPDE engine require locking in the same way the BASE engine does? And, if so, how is this accomplished? (If you're interested, here is a good paper on why you'd want to use the lock statement during ETL processing: www.lexjansen.com/pharmasug/2005/posters/po33.pdf) Regards, Scott

ScottBass · ‎09-29-2011

SAS 9.2 on Windows with Active Directory Authentication I've changed some permissions on library objects. The permissions look OK for my login, but I would like to test how they would look for someone else on my team. I created a temporary internal ID for his account, refreshed the spawner, and started EG. I can connect to the MDS as him, but can't start the Workspace Server. Note: I can, however, RMB on the WSS and select libraries. Is this sufficient to test the security? The Object Spawner error log is: 2011-09-29T20:01:24,507 ERROR [00000991] xxxxxxxx@saspw - No host credentials exist to start this server. Either the client needs to send in host credentials, or credentials need to be specified for the server. OK, that makes sense. I also tried saving a password in the user object, hoping it would bypass the AD authentication. OK, I didn't really think it would work, just telling you what I did. Is there a way I can impersonate a user and spawn a WSS? Thanks, Scott

ScottBass · ‎09-29-2011

Hi, Just to clarify, we are DIS-interested in using DIS, so we don't have it. I'm using SMC. Copying them to a new folder would create new metadata object ID's, which in DIS would break the jobs. However, since they are 1) new tables not yet references elsewhere, and 2) only there so they show up in EG, your suggestion could work for me. Or I guess I could just delete the tables and reimport them, paying attention to that silent, grayed out option setting to select the metadata folder. But moving en masse via SMC in an obvious way via the user interface would be nice. Scott

ScottBass · ‎09-29-2011

Hi, Maybe I'm missing something too... A trivial example to illustrate: %macro create_a_variable(value=); attrib myvar length=8 format=best. label="My Variable"; myvar=&value; %mend; data one; %create_a_variable(value=1); %create_a_variable(value=2); %create_a_variable(value=3); run; The macro is "self-contained" - all required variables are declared ***within the macro*** via attrib, and I don't have to keep a flag when myvar is "declared". %create_a_variable executes an attrib statement, which is a compile time option. Now, I know myvar gets specified three times during compilation, and last setting wins, but I don't get an error when I try to redefine myvar, esp. when the attributes are the same. So why do I get an error when I try to declare hash h multiple times in the data step? No, I wouldn't do this in open code, but it just makes life easier (and consistent with other aspects of SAS) if I don't get a compile time error if I execute "declare hash h" multiple times within a generic macro. Are there technical reasons, or object oriented programming convention, that caused SAS to write the software this way? Or is it just the way they decided to do it? Apologies if I'm just being dense...it happens occasionally. Scott

ScottBass · ‎09-29-2011

Hi, SAS 9.2 on Windows... From the Data Library Manager plugin, I RMB'd on one of the library objects, selected Register Tables, then Next. I didn't pay attention to the metadata folder location (Note to SAS: it would be nice if you asked me to confirm the folder location, rather than silently assuming I want them in /Shared). So now I have a bunch of table objects in /Shared. How do I move them en masse to my desired metadata folder? From the Folders tab, doing them one by via via properties will take forever, selecting multiple items doesn't work, and neither does drag and drop. Thanks, Scott

ScottBass · ‎09-26-2011

OK, this request may be far fetched, but... We have to write a macro that is a "wrapper" macro for another macro (provided by Futrix). This wrapper macro will provide additional error checking and, in summary, provide an easier user interface to the Futrix provided macro. We also want to create a stored process to provide a GUI to set the macro parameters. So, a "programmer" will just call the macro in his/her code. A "business user" will call the SP to display the prompts and set the parameters, and the SP will be a "one line" call to the macro. The result is an output dataset. The parameters go like: select a source table, select columns from that table, create a where clause. For the where clause, it would be cool if I could somehow call the EG where clause window, build a where clause, and have that where clause passed into a macro variable. Or, has anyone ever written a where clause generator using the prompting capability of SAS stored processes? Alternatively, has anyone created an EG custom task similar to the above? It would be very much like the EG query builder, but instead of submitting code to SAS, it would set macro variables that I could pass to the macro. Thanks, Scott

ScottBass · ‎09-23-2011

Hi Peter, Thanks for your reply(ies), much appreciated. Let me restate what you said above to make sure I understand what you're saying... 1) Use the SPDE engine over the BASE engine for my scenario. 2) Partition my data by year. For example (pseudocode and untested): libname spde spde "some path"; data spde.mydata_2008 spde.mydata_2009 spde.mydata_2010 spde.mydata_2011; set libref.mydata; select(transaction_year); when(2008) output spde.mydata_2008; when(2009) output spde.mydata_2009; when(2010) output spde.mydata_2010; when(2011) output spde.mydata_2011; end; run; 3) Index the separate SPDE datasets "appropriately" based on the typical queries the end users make. 4) If one of those indexes is unique, i.e. the concatenation of the keys uniquely identifies a record, then I can use this code to update/insert *: proc append base=spde.mydata_2011 data=work.transaction_data_2011 uniqsave=rep; run; Don't use this technique if transaction_data_2011 is "large", i.e. it's a "bulk" update. 5) By splitting the data by year, we can parallel process the updates, i.e. schedule multiple SAS transaction load jobs to run concurrently. 6) What my colleague (ok, my boss) suggested was to create separate data step views for ALL the permutations of the years. For example (pseudocode): v_mydata_all (all years) v_mydata_2008_2009_2010 v_mydata_2008_2009 v_mydata_2009_2010_2011 v_mydata_2009_2010 v_mydata_2010_2011 etc (I may have missed some combination) Then, the end user would have to select the correct view when doing their analysis (both in EG, Futrix, and Stored Process Web Application) to get their desired performance. Which IMO is not ideal. Instead, are you suggesting that a single proc sql view defined as above (outer union corresponding) will get acceptable performance due to the underlying SPDE dataset and related indexes? (That would be great). Did you mean to use different librefs in your proc sql view, rather than different datasets? Would it be "smart enough" to stop reading data when a where clause is filtering by year (eg. transaction_year in (2009,2010). What about transaction_year in (2009, 2011), i.e non-consecutive (probably wouldn't happen, but...) Can you comment on why an SQL view will be more performant than a data step view? Keep in mind that the main reason for this performance tuning/data restructure is to give better perfornance for interactive queries; we can live with current batch job perfornance, although that could change in the future as data volumes increase. I mention this to emphasize that the easier the architecture is for the end users, the better. * As an aside, the proc append functionality reminds me of an Oracle "upsert" (update / insert). It's an update technique which first tries an update, then an insert if the key was not found. Google "Oracle upsert" for more details. Please let me know if I've misunderstood what you've stated above. Thanks, Scott

Online Status	Offline
Date Last Visited	‎03-11-2021 01:34 AM

Re: How do I unsubscribe from a topic?

Re: How do I unsubscribe from a topic?

How do I unsubscribe from a topic?

Re: How to: Listing all the files in a folder

Re: How to: List all the files in a folder

Re: How to improve performance in Data step where clause

Re: Using SAS to find a streak of occurrences within any seven day win...

Re: Proc transpose error with same name variable and exceeds 32 length

Re: using rsubmit in a loop

Re: using rsubmit in a loop

Re: LOCF

Re: Programming Question

Re: Programming Question

Re: SAS query - Split data to different dataset

Re: SAS query - Split data to different dataset

Re: Why the result useing the catx have blanks

Re: Check numeric values in alphanumeric variables

Re: Read a Variable Value into a Macro Variable

Re: How to: List all the files in a folder

EG Custom Task to play a sound

How to create a working outline for SAS Global Forum

SAS Global Forum Presenter Mentoring Program

SQL creates bogus warning message

Quoting program name & file path argument in filename pipe on Windows

Re: Do I need to lock a SPDE dataset during ETL processing?

Re: Do I need to lock a SPDE dataset during ETL processing?

Re: Question about Hash Tables

Re: Do I need to lock a SPDE dataset during ETL processing?

Do I need to lock a SPDE dataset during ETL processing?

Question about Hash Tables

Do I need to lock a SPDE dataset during ETL processing?

How can I impersonate another user?

How do I move tables objects en masse to another metadata folder?

Re: Deleting a hash object and reusing the object reference variable

How do I move tables objects en masse to another metadata folder?

Can I call the EG where clause generator from a stored process?

Re: How is the SPDE MINPARTSIZE system option meant to work?

SAS Global Forum 2020