About BillJones

BillJones · ‎12-19-2014

Kurt, Thanks for the tip. I always search on google, sas.com, and stackoverflow.com before creating a post. However, in this case, it appears that I missed some useful information. I'll do a more diligent search next time. -Bill

BillJones · ‎12-18-2014

DBailey, No problem! Thanks again for the code. Here's another question for the group. Is there any disadvantage to using a libname statement and then referencing it in your proc sql? For example, libname SPINNDW oledb provider=sqloledb schema=dbo user=&mkt01_user. password=&mkt01_pass. datasource=MKT01 properties=('initial catalog'=prodCSE_dw) access=readonly; proc sql; create table policy_inforce_amt as select pol.reportperiod, pol.policyref, pol.annualstatementlinecd, sum(pol.inforceamt) as ye_inforceamt from spinndw.policysummarystats pol where pol.reportperiod in ('201012','201112','201212','201312','201408') and pol.feecd is null group by pol.reportperiod, pol.policyref, pol.annualstatementlinecd order by pol.policyref, pol.reportperiod ; quit;

BillJones · ‎12-18-2014

Kurt, Thanks for the code. I tried your suggestion and it's running! DBailey, Thanks for your thoughts and alternative method. I am using 9.4. However, when I try use your syntax, I get a data link popup. Is there a way to get rid of the popup? Thanks again, Bill

BillJones · ‎12-18-2014

I'm trying to connect directly to an oledb database through proc sql. I have a libname statement that works. However, when I try to use the same syntax in the sql, it doesn't work. Please see the log file below. Any suggestions would be greatly appreciated. Thanks very much, Bill log file: 650 dm 'clear log'; 651 652 options obs=max mprint=1; 679 680 libname SPINNDW oledb provider=sqloledb schema=dbo user=&mkt01_user. 680 ! password=&mkt01_pass. datasource=MKT01 properties=(XXXXXXXXXXXXXXXXX=XXXXXXXXXX) 680 ! access=readonly; NOTE: Libref SPINNDW was successfully assigned as follows: Engine: OLEDB Physical Name: sqloledb 681 /************************* 682 Create rolled-up table for inforce premium & inforce count 683 *************************/ 684 685 686 proc sql; 687 connect to oledb as spinndw (init_string="oledb provider=sqloledb schema=dbo 687 ! user=&mkt01_user. password=&mkt01_pass. datasource=MKT01 properties=('initial 687 ! catalog'=prodCSE_dw) access=readonly"); ERROR: Error trying to establish connection: Unable to Initialize: Invalid authorization specification: Invalid connection string attribute 688 create table policy_inforce_amt as select * from connection to spinndw 689 (select 690 policy_inforce_amt as select 691 pol.reportperiod, 692 pol.policyref, 693 pol.annualstatementlinecd, 694 sum(pol.inforceamt) as ye_inforceamt 695 from 696 spinndw.policysummarystats pol 697 where 698 pol.reportperiod in ('201012','201112','201212','201312','201406') 699 and pol.feecd is null 700 group by 701 pol.reportperiod, 702 pol.policyref, 703 pol.annualstatementlinecd 704 order by 705 pol.policyref, 706 pol.reportperiod 707 ); ERROR: The SPINNDW engine cannot be found. ERROR: A Connection to the spinndw DBMS is not currently supported, or is not installed at your site. 708 disconnect from spinndw; ERROR: Connection to the spinndw DBMS does not exist. 709 quit; NOTE: The SAS System stopped processing this step because of errors. NOTE: PROCEDURE SQL used (Total process time): real time 0.03 seconds cpu time 0.03 seconds

BillJones · ‎12-16-2014

FriedEgg, Thank you for your thoughts and code. I prefer cURL over proc http because I can simultaneously submit up to 3 requests at once. However, I may try proc http if I run into significant issues accessing the api via cURL. Also, I try to carefully count the quotes to see if I can construct the header without any functions. Thanks again, Bill

BillJones · ‎12-16-2014

data _null_, Thanks very much for the additional code! -Bill

BillJones · ‎12-16-2014

Thanks for the response and yes I need the commas. They're required for the OAuth header.

BillJones · ‎12-15-2014

Quick follow-up question: How do I add additional quoted strings to the pipe statement. For example, I tried to add a consumer_secret key with code below. %let consumer_key=cheezwhiz; %let consumer_secret=coorslight; %let header = %sysfunc(quote(curl --header %sysfunc(quote(Authorization: OAuth oauth_consumer consumer_key=%sysfunc(quote(&consumer_key)),consumer_secret=%sysfunc(quote(&consumer_secret)))))); %put NOTE: %superq(header); filename curl pipe &header.; filename curl list; The code runs, but I cannot see the second key (consumer_secret) in the log. What's the best way to deal with this issue? Should I use the catx function? Or is there a more direct solution? -Bill

BillJones · ‎12-08-2014

data_null_, Thanks so much for the code! The quote function is definitely having all the fun now. Thanks again, Bill

BillJones · ‎12-07-2014

Hello everyone I'm trying to call cURL within SAS to access an api with oauth 1.0. In order to this, I need to construct an appropriate header. Unfortunately, I cannot specify the header correctly in SAS because of the quotes. I've read several posts here and on other sites about how to handle quotes within a macro variable. My code forms a macro variable with the necessary quotes. The issue is when I put it in the filename statement. I get an error. %let consumer_key=%quote(")cheezwhiz%quote("); %let header = "curl --header 'Authorization: OAuth oauth_consumer consumer_key=&consumer_key.'"; %put &header; gives "curl --header 'Authorization: OAuth oauth_consumer consumer_key="cheezwhiz"'" which is exactly what I want. However, when I try to execute this within a filename statement, e.g., filename curl pipe &header.;, I get the following error message: ERROR 23-2: Invalid option name cheezwhiz. Does anyone know of a way to circumvent this error? Thanks very much for any suggestions. -Bill

BillJones · ‎12-03-2014

Jaap/Nikola, Thanks for your thoughts! We'll investigate the caching. Server OS is Windows Server 2012 R2. I'll have our IT staff evaluate fio. Thanks again, Bill

BillJones · ‎12-03-2014

Hello Everyone, The company that I work for recently installed SAS on a server. Technical specifications are as follows: Flex node IBM 8737 x 240 2 x 12 core intel processors 32GB RAM IBM SAN Storwize v7000: RAID 5 on spindle disks and RAID 1 SSD mirror Currently when I run SASIOTEST, it shows writes speeds of 400+mbs/sec and read is less than 100/mbs/sec??? Note I'm working with both my IT department and SAS to resolve this issue. As of yet, nobody has come up with a solution. I'm posting here on the forum in the hope that one of you have encountered a similar situation and were able to improve performance. Any suggestions would be greatly appreciated. Thank you.

BillJones · ‎07-02-2014

Reeza, Sort of. I want a library which shows for each variable the following format info: fmtname type start end label hlo eexcl sexcl. The code above gives me a file with fmtname type start end label hlo eexcl sexcl, but not for each variable. How can I get the format info by variable? Is there a by statement that I can employ within proc format? Or do I need to write a macro? Thanks very much! -Bill

BillJones · ‎07-02-2014

Reeza, Thanks very much for the response. Do you mean something like: proc format cntlout = in.&filename; run; proc format cntlin = in.&filename library=library; run; -Bill

BillJones · ‎07-02-2014

THANK YOU VERY MUCH everyone for the responses. I really appreciate the help. SASKiwi, I definitely want to create a macro solution. Like I mentioned above, I have approximately 30 tables where I want to take the existing format information and output it to a library. Tom, What I'm trying to do is a create a "2-level name of format library", so I can run proc_codebook, which is macro program that gives useful information on a given data set. I was hoping to run it on a large number of tables that recently got handed over to me. However, when I try to create the format library necessary to run the code, I get an error. Link to proc_codebook: http://www.cpc.unc.edu/research/tools/data_analysis/proc_codebook/proc_codebook.sas I guess a broader question would be is there a more recent "codebook" available online? Perhaps, I can just use that instead? jwillis, Tweaked your code a bit to see if I could get it to work for one of the data files that I have. Still tweaking. I'll probably have to something like proc contents to list out all the variables? Thanks again everyone for your thoughts. -Bill

Online Status	Offline
Date Last Visited	‎08-13-2020 06:16 AM

Re: Using sas/secure to generate hmac-sha1 oauth signature

Re: Using sas/secure to generate hmac-sha1 oauth signature

Re: Using sas/secure to generate hmac-sha1 oauth signature

Re: proc http & ssl

Re: proc http & ssl

Re: proc http & ssl

Re: proc http & ssl

Re: proc http & ssl

Re: proc http & ssl

Re: Non Standard Regression

Re: proc http & ssl

Re: Parse json file with Proc Groovy

Re: Using sas/secure to generate hmac-sha1 oauth signature

Re: proc sql connect to oledb

Re: proc sql connect to oledb

Re: Merging two datasets with different but similar columns

Re: proc sql connect to oledb

Re: proc sql connect to oledb

Re: proc sql connect to oledb

proc sql connect to oledb

Re: Filename quote fun!

Re: Filename quote fun!

Re: Filename quote fun!

Re: Filename quote fun!

Re: Filename quote fun!

Filename quote fun!

Re: I/O Issues: read slower than write

I/O Issues: read slower than write

Re: PROC FORMAT: How to make a library from an existing data set?

Re: PROC FORMAT: How to make a library from an existing data set?

Re: PROC FORMAT: How to make a library from an existing data set?