About Quentin

Quentin · ‎10-16-2012

Hi All, I am working on a stored process that will be called from Excel Add-In, and will return a table to Excel. The ODS destination is tagsets.sasreport12. Having a problem where if Excel thinks the value in a cell is numeric, it treats it as such, and does not preserve leading/trailing zeroes. So a character value of '01.0' in SAS becomes an Excel value of 1. I have read the very helpful paper, Dont Gamble with Your Output: How to Use Microsoft Formats with ODS, and that describes how to use TAGATTR when destination is tagsets.ExcelXP and HTMLSTYLE for HTML destinations. Is there something similar I could use when the destination is tagsets.SASreport12 ? I was able to preserve zeroes by prepending a single quote mark to character values, which I guess is a way in Excel to force a value to be text. But then I get an ugly warning from Excel that I have a number formatted as text. I suppose I may be better off switching to an HTML destination so that I can use HTMLSYLE? But just wanted to ask, before giving up on SASreport. Appreciate any suggestions. Thanks, -Q.

Quentin · ‎10-08-2012

Correct, you are. Sorry for mis-reading your first post. I was looking at &jobid in _global_ scope. &SYSjobid in _automatic_ scope indeed has the PID, as you said. Good to know. Maybe I'll start dumping all the _automatic_ vars to the log as a matter of course as well. Reallly appreciate all your help with this. --Q.

Quentin · ‎10-08-2012

PS command was very helpful. Screenshot of output is below. The first 3 rows are a job I scheduled to start at 12:44 today. The 4th row is the ps command, issued at 12:52, so I was 8 minutes into the job. No idea what the Oct 5 process is... Assume next 3 lines are my putty session/samba The last 3 lines are the job that triggered by original question. The job I started that never ended. Looks like it's not taking any CPU, but it's still waiting. Oracle DBA doesn't see anything running, so I think this is a zombie process, suspect I'll have to kill it myself. Also noticed the /.lsbatch directory above. Looks like this is a temporary directory used by LSF, and written to my home dir. Thanks Patrick! --Q.

Quentin · ‎10-08-2012

Thanks again Patrick, Looks like "ps -elf" does show more useful information than "top." Will have to read up on PS to understand the output.... : ) I generally dump all the global macro variables to the log at the beginning of any job running on a BI platform. If only to astound myself with the vast number of global macro variables created. Unfortunately &JOBID doesn't look like a PID to me. (But I'm pushing well past my linux knowledge). I get &JOBID=A5PKNKGJ.BQ000029 (With some quoted symbols around it, probably parentheses or quote marks). I'm guessing this is SAS job id used in SAS metatadata. There is a similar &TRANSFORMID. Don't see anythings that looks like linux pid. --Q.

Quentin · ‎10-08-2012

Thanks for the links, Patrick. Agree, often when you see a .lck file it suggest the SAS job is still running. But you can also see .lck files remaining when a job has terminated in a surprising manner. The same way you can see files left in a WORK dir when SAS doesn't exit nicely. Pretty sure in this case the job had actually crashed. Will be playing with it more this week. To Linus's point, am definitely working with the DBA. Of course, even in 2012, BIG data = long job times, or at least BIG data + slow servers + slow networks = long job times. : ) --Q.

Quentin · ‎10-04-2012

Thanks for the suggestion. We are using LSF. I managed to find sbatchd.log.servername, which has a modified date of exactly when it looks like my process aborted, and this suspicious line: 7.06 lsbatchDir(): neither /.lsbatch nor spool dir could be used by the job , temp dir will be used Not sure what that means, but it looks like this log doesn't get written to much, so will send this off to our SAS admin and see if it helps. Would welcome any other suggestions. Thanks, -Q.

Quentin · ‎10-04-2012

Hi, I'm new to DI studio. I have a simple job that runs a pass-through query against an Oracle database. It's a big query, so I expect it to run a few hours. I scheduled it to run over night. So in DI studio I created a job, which became a flow, and I scheduled the flow in SMC. But the whole job is just the one step that runs pass-through query with user-written code. This morning, I come in to see how everything went, and I'm not sure. Either the job aborted after an hour, or it's still running. : ) On the SAS server, I can see the log file from the job in ..../Lev1/SASApp/BatchServer/Logs It is stopped right at the point of the pass through query, i.e.: The log stops at the pass-through query: MPRINT(QUERY): proc SQL; MPRINT(QUERY): Connect to oracle(PATH=XXXXXX UID=XXXXXXX PASSWORD="XXXXXXXXXXXX"); MPRINT(QUERY): Create Table out.mydata As Select * from Connection to oracle ( select ... And that is where the log ends. At the select statement. Which I might expect to see if the query were still running on Oracle, and had not yet been returned to SAS server. If I look for out.mydata on the SAS server, I see the temporary file, mydata.sas7bdat.lck. It looks like just a shell dataset, and mod time is the time the job started. Also what I would expect to see if job was in progress. I don't have access to the Oracle server to see if anything is running there. When I run TOP command on the SAS server (linux) I don't see any processes that look like this query. The mod date on the log is almost exactly 1 hour after the job started, making me wonder if this somehow timed out on the (SAS server or on Oracle side) and was aborted. Anyway, looking for suggestions on how to go about diagnosing this, particulary if there is a way for me to check the status of job from SAS server side, or if I need to call the oracle DBA and ask them to start reviewing those logs. Thanks, --Q.

Quentin · ‎10-03-2012

Hi, When you define a stored process prompt, you can assign an unformatted value, and formatted value. So you have maybe 0=No 1=Yes When the stored process runs, the unformatted values are passed via macro variables. Was wondering, is there an easy way to get the formatted values? (e.g. is there an actual format catalog sitting somewhere that has this in it?, or somewhere in the metadata?). Of course I built the prompt values, so can use the same data to build my own format. But was curious if this was already sitting somewhere. Planning to use this with a SP called from SPWA, and want to return a subtitle which displays some of the user provided prompt values in language they will recognize. Thanks, -Q.

Quentin · ‎10-02-2012

Another option would be to redirect the logs in SAS, rather than your script, using PROC PRINTTO. --Q.

Quentin · ‎09-28-2012

Hi All, Short version: What are the "best" way(s) to have a stored process called through SPWA return a SAS dataset to the user (as an attachment)? Long version: I have a big SAS dataset, and want to write a stored process that will let users build their own data extracts. Basically it will be a query tool. User will pass a few parameters for a WHERE statement, and specify to return the file as a csv, .xls, or .sas7bdat. When the stored process runs, it should pop up the open/save dialog box. Stored Proces will be called through SPWA. For the csv file I assume I will just use ods CSV writing to _webout (after the appropriate stpsrv_header calls). And since I don't need any formatting in the Excel sheet, I think for Excel my first shot would be just returning a csv file, with an .xls extension and stpsrv_header telling IE to open it in Excel. I don't need any of the pretty reporting stuff that comes with tagsets.ExcelXP, as this is data, not a report. But I'm not sure how to send back a SAS dataset. One thought was to try using a data _null_ step to stream back the .sas7bdat file, as in: http://support.sas.com/kb/6/588.html. I've used that approach for streaming a pdf report, and it has worked well. But maybe I would be better off looking into how to send this as a package instead? Would appreciate any suggestions/recommendations. Thanks, --Q.

Quentin · ‎09-25-2012

Agree with Art's summary. I rewrote your step with some put statements to show the values on each iteration of the do-loop. 14 data star; 15 set star; 16 17 array var[1] star; 18 array result st01-st09; 19 20 do i=1 to 9; 21 lag1=lag(var[1]); 22 lag2=lag2(var[1]); 23 lag3=lag3(var[1]); 24 put (_n_ star i lag1 lag2 lag3)(=); 25 end; 26 27 run; _N_=1 star=0 i=1 lag1=. lag2=. lag3=. _N_=1 star=0 i=2 lag1=0 lag2=. lag3=. _N_=1 star=0 i=3 lag1=0 lag2=0 lag3=. _N_=1 star=0 i=4 lag1=0 lag2=0 lag3=0 _N_=1 star=0 i=5 lag1=0 lag2=0 lag3=0 _N_=1 star=0 i=6 lag1=0 lag2=0 lag3=0 _N_=1 star=0 i=7 lag1=0 lag2=0 lag3=0 _N_=1 star=0 i=8 lag1=0 lag2=0 lag3=0 _N_=1 star=0 i=9 lag1=0 lag2=0 lag3=0 _N_=2 star=0 i=1 lag1=0 lag2=0 lag3=0 _N_=2 star=0 i=2 lag1=0 lag2=0 lag3=0 _N_=2 star=0 i=3 lag1=0 lag2=0 lag3=0 _N_=2 star=0 i=4 lag1=0 lag2=0 lag3=0 _N_=2 star=0 i=5 lag1=0 lag2=0 lag3=0 _N_=2 star=0 i=6 lag1=0 lag2=0 lag3=0 _N_=2 star=0 i=7 lag1=0 lag2=0 lag3=0 _N_=2 star=0 i=8 lag1=0 lag2=0 lag3=0 _N_=2 star=0 i=9 lag1=0 lag2=0 lag3=0 _N_=3 star=0 i=1 lag1=0 lag2=0 lag3=0 _N_=3 star=0 i=2 lag1=0 lag2=0 lag3=0 _N_=3 star=0 i=3 lag1=0 lag2=0 lag3=0 _N_=3 star=0 i=4 lag1=0 lag2=0 lag3=0 _N_=3 star=0 i=5 lag1=0 lag2=0 lag3=0 _N_=3 star=0 i=6 lag1=0 lag2=0 lag3=0 _N_=3 star=0 i=7 lag1=0 lag2=0 lag3=0 _N_=3 star=0 i=8 lag1=0 lag2=0 lag3=0 _N_=3 star=0 i=9 lag1=0 lag2=0 lag3=0 _N_=4 star=0 i=1 lag1=0 lag2=0 lag3=0 _N_=4 star=0 i=2 lag1=0 lag2=0 lag3=0 _N_=4 star=0 i=3 lag1=0 lag2=0 lag3=0 _N_=4 star=0 i=4 lag1=0 lag2=0 lag3=0 _N_=4 star=0 i=5 lag1=0 lag2=0 lag3=0 _N_=4 star=0 i=6 lag1=0 lag2=0 lag3=0 _N_=4 star=0 i=7 lag1=0 lag2=0 lag3=0 _N_=4 star=0 i=8 lag1=0 lag2=0 lag3=0 _N_=4 star=0 i=9 lag1=0 lag2=0 lag3=0 _N_=5 star=0 i=1 lag1=0 lag2=0 lag3=0 _N_=5 star=0 i=2 lag1=0 lag2=0 lag3=0 _N_=5 star=0 i=3 lag1=0 lag2=0 lag3=0 _N_=5 star=0 i=4 lag1=0 lag2=0 lag3=0 _N_=5 star=0 i=5 lag1=0 lag2=0 lag3=0 _N_=5 star=0 i=6 lag1=0 lag2=0 lag3=0 _N_=5 star=0 i=7 lag1=0 lag2=0 lag3=0 _N_=5 star=0 i=8 lag1=0 lag2=0 lag3=0 _N_=5 star=0 i=9 lag1=0 lag2=0 lag3=0 _N_=6 star=-1 i=1 lag1=0 lag2=0 lag3=0 _N_=6 star=-1 i=2 lag1=-1 lag2=0 lag3=0 _N_=6 star=-1 i=3 lag1=-1 lag2=-1 lag3=0 _N_=6 star=-1 i=4 lag1=-1 lag2=-1 lag3=-1 _N_=6 star=-1 i=5 lag1=-1 lag2=-1 lag3=-1 _N_=6 star=-1 i=6 lag1=-1 lag2=-1 lag3=-1 _N_=6 star=-1 i=7 lag1=-1 lag2=-1 lag3=-1 _N_=6 star=-1 i=8 lag1=-1 lag2=-1 lag3=-1 _N_=6 star=-1 i=9 lag1=-1 lag2=-1 lag3=-1 NOTE: There were 6 observations read from the data set WORK.STAR. NOTE: The data set WORK.STAR has 6 observations and 14 variables HTH --Q.

Quentin · ‎09-24-2012

You are right Chris. I should have paused to guess at why the autocomplete thought there were some odd macro vars. I'm in the habit of commenting my macro variables as below: %global file /* Fileref|Filename|Filepath/filename */ method ; While SAS respects those comments, looks like EG autocomplete word scanner (?) thinks everything before the semicolon is the name of a macro var, as in below simple example. Guess some of that junk was tripping up how it autcompletes, causing the off-by-one (not shown below). When I took out the comments, it started working again. Thanks much!

Quentin · ‎09-24-2012

Hi All, I just noticed having an odd problem with EG code autocomplete. It seems to be off by one. So if I type &file and hit space, it selects &extension, and replaces it. If I start to type &method , after I have typed &me it guesses I'm typing &just Any idea what could be going wrong? I have quit and restarted EG. And turned autocomplete off and on. Seems to be happening only in one program in the project. Thanks, --Q.

Quentin · ‎09-21-2012

Hi, I played with this a bit, but wasn't successful. : ) I was thinking to just write the PDF and Excel file to work directory, then stream them back to the user. I was playing with something like: ods tagsets.excelxp file="%sysfunc(pathname(work))/myExcel.xls"; proc print data=sashelp.class; run; ods tagsets.excelxp close; ods pdf file="%sysfunc(pathname(work))/MyPDF.pdf"; proc print data=sashelp.shoes; run; ods pdf close; data _null_; file _webout (no_bottom_matter); infile "%sysfunc(pathname(work))/myExcel.xls"; if _n_ = 1 then do; rc = stpsrv_header('Content-type','application/vnd.ms-excel'); rc = stpsrv_header('Content-disposition',"attachment; filename=MyExcel.xls"); end; input ; put _infile_; run; data _null_; file _webout (no_top_matter) recfm=s; infile "%sysfunc(pathname(work))/MyPDF.pdf" recfm=n; if _n_ = 1 then do; rc = stpsrv_header('Content-type','application/pdf'); rc = stpsrv_header('Content-disposition',"attachment; filename=MyPDF.pdf"); end; input c $char1.; put c $char1. @@; run; But that doesn't work, I suspect for a number of reasons. Basically trying to send two posts (?) back to the browser. And not allowed 2 different headers. Even when I tried just 2 pdf files, couldn't get it working. Looks like no_bottom_matter can't be used with file _webout ??? Anyway, despite my lack of success, I think if you are willing to keep the approach of having 2 different stored processes, something like above might work, if you use sessions. So the first stored process writes the 2 reports to work dir, and streams back the Excel file. The second stored proecss connects to the same session as the first stored process (so can see the 2 reports in the work dir), and streams the pdf file back to the user. It's just a thought, I haven't played with sessions yet. Hopefully something in this will help, or perhaps encourage someone else to jump in. --Q. .

Quentin · ‎09-08-2012

Hi, The count+1 thing is it's own type of statement, it's a SUM STATEMENT. It needs to be a plus sign. There is no equivalent DECREMENT statement. If you want to do the same (set to 0, retain, use sum function) but decrement, you can do: count + - 1; or count + (-1); HTH, --Q.

Online Status	Offline
Date Last Visited	yesterday

Re: How to Reg on each row?! with Slope/Intercept saved out?!

Re: How to Reg on each row?! with Slope/Intercept saved out?!

Re: Report with 1000 days from now

Re: problem with ODS in SAS EG 8.3

Re: problem with ODS in SAS EG 8.3

Re: Create a catalog table

Re: Combine the coding on arrays in the same data step

Re: How to use a macro variable in a if else condition

Re: How to use a macro variable in a if else condition

Re: SAS macro in conjunction with PROC PYTHON

Re: SAS 9.4M8 and Git Integration

Re: VALIDVARNAME=V7

Re: How to Reg on each row?! with Slope/Intercept saved out?!

Re: sending emails from sas

Re: PROC COMPARE label attribute is missing

Re: Use of Infile and Input statements

Re: SAS macro in conjunction with PROC PYTHON

Re: send email +attach log file only in case of error or warning

Re: chart representing thestructure of data

Re: send email +attach log file only in case of error or warning

Preserve leading zeroes in STP called by Add-In

Re: How to Diagnose Aborted DI Studio Job

Re: How to Diagnose Aborted DI Studio Job

Re: How to Diagnose Aborted DI Studio Job

Re: How to Diagnose Aborted DI Studio Job

Re: How to Diagnose Aborted DI Studio Job

How to Diagnose Aborted DI Studio Job

Is formatted value of prompts available?

Re: Change to VBScript in EG 4.3

Return SAS Dataset from Stored Process

Re: Lag function inside a Do Loop

Re: EG 4.3 autocomplete off by one

EG 4.3 autocomplete off by one

Re: How do you create a package?

Re: Trouble using the minus sign for counting down a variable

SAS Inner Circle Panel

SUGA

SAS User Groups

SAS Global Forum 2017

SAS Explore