About BillJones

BillJones · ‎07-02-2014

Thanks very much for the response. Is there code to automatically generate the required variables? In the example you referenced, there's a data step where the required variables for proc format are created. I have probably 30 tables where I want to create libraries that capture the existing format information. -Bill

BillJones · ‎07-02-2014

Hello, I have a data set where I want to take the current format info and output it to a format library. Specifically, I'm interested in the following variables: fmtname type start end label hlo eexcl sexcl. My apologies if this is a really basic question. Here's the code that I currently working with: proc format cntlin = in.&filename library = library; run; However, whenever I run this, I get the following error: ERROR: Missing FMTNAME variable. ERROR: Missing START variable. ERROR: Missing LABEL variable. Any suggestions would be greatly appreciated. Thanks very much. -Bill

BillJones · ‎07-01-2014

Kurt, Thank you for your thoughts! At least now I'll know what to request. I'll definitely shoot for 500 MB/s. We'll see what IT says. Regards, Bill

BillJones · ‎06-29-2014

crikriek, You need a unique key on both data sets to perform the merge. I don't know your data, but I imagine something like symbol id_trade should work. If you can get those variables on both files, then you can use code like the following: proc sort data=trades nodupkey; by symbol id_trade; run; *check log to ensure there are no dups; proc sort data=spread nodupkey; by symbol id_trade; run; *check log to ensure there are no dups; data trade_info; merge trades (in=a) spread (in=b); by symobl id_trade; if a; run; Code above performs a left outer join. IOW, it keeps all the records from data set a (trades) and attaches information from data set b if the keys match. If you want an inner join which only keeps those records that match then use "if a & b;" instead of "if a;" -Bill

BillJones · ‎06-27-2014

Kurt, Thanks very much for the information. I believe that the I/O environment on our new server will be a SAN. Therefore, we'll just have to be sure that we get enough throughput. How much throughput would you recommend for a small shop of SAS users (lt 6) working with data sets in the millions of records (uncompressed large file would be ~200gb)? Regards, Bill

BillJones · ‎06-25-2014

Nice! Will do. Thanks for the tip.

BillJones · ‎06-25-2014

Kurt, Thanks very much for the analysis. The box that I'm running SAS on has 2 HDDs (both 7200 RPM). I'll create a temp sas data folder on the other drive to write large data sets. Hopefully, this will increase performance. I would just purchase an SSD, but my firm plans on upgrading to a server soon. -Bill

BillJones · ‎06-25-2014

SASKiwi, Thanks. Already using COMPRESS=BINARY with PROC DATASETS and the DATA step. Really shrinks the size of the files. Regards, Bill

BillJones · ‎06-23-2014

SASKiwi, Thanks for the suggestion. I created a macro using the code you posted. Tried it on a sample of 1000 obs and seems to execute quickly. I'm running it now on all the data and will post the results. -Bill

BillJones · ‎06-23-2014

SASKiwi, Thanks for your thoughts. I did a simple data step where I appended several files into one file. Now the data files were large (total size of the combined file was 204GB). However, the job took 75 minutes. I can see perhaps 30 minutes for something like this, but over an hour? It seems to be taking too long. Note the hard disk is a Hitachi 7200 RPM. If the issue is IO, how do I fix it? Adjust the BLKSIZE? Thanks again, Bill Code: data data_exp.policysummarystatsraw; set data_exp.policysummarystats2008_ data_exp.policysummarystats2009_ data_exp.policysummarystats2010_ data_exp.policysummarystats2011_ data_exp.policysummarystats2012_ data_exp.policysummarystats2013_ data_exp.policysummarystats2014_; run; Log: NOTE: There were 30051 observations read from the data set DATA_EXP.POLICYSUMMARYSTATS2008_. NOTE: There were 25949456 observations read from the data set DATA_EXP.POLICYSUMMARYSTATS2009_. NOTE: There were 29754796 observations read from the data set DATA_EXP.POLICYSUMMARYSTATS2010_. NOTE: There were 30672849 observations read from the data set DATA_EXP.POLICYSUMMARYSTATS2011_. NOTE: There were 32867276 observations read from the data set DATA_EXP.POLICYSUMMARYSTATS2012_. NOTE: There were 19762534 observations read from the data set DATA_EXP.POLICYSUMMARYSTATS2013_. NOTE: There were 1535814 observations read from the data set DATA_EXP.POLICYSUMMARYSTATS2014_. NOTE: The data set DATA_EXP.POLICYSUMMARYSTATSRAW has 140572776 observations and 172 variables. NOTE: DATA statement used (Total process time): real time 1:15:51.35 user cpu time 2:43.30 system cpu time 8:36.80 memory 1565.71k OS Memory 8632.00k Timestamp 06/23/2014 04:27:35 PM

BillJones · ‎06-23-2014

Hello, I'm running SAS 9.3 on a Windows 7 64 machine with 24GB of RAM and 2 x Intel Xeon 2.4GHz processors. My jobs seem to take forever. I checked the amount of RAM and CPU usage in the Windows Task Manager, and SAS is using approximately 75MB of RAM and only 13% of the CPU. Note I changed the memsize option in the config file from 2GB to 18GB and also moved up the sortsize to 13.5GB. Why is the RAM/CPU usage so low? Are there some tweaks that I can make to improve performance? Adjust buffersize? I've attached the output from running proc options. Thanks very much for any suggestions. -Bill

BillJones · ‎06-13-2014

Wow, thank you everyone for all the suggestions. I appreciate your thoughts. I didn't specify the READ_LOCK_TYPE=NOLOCK option, so that wasn't the issue. I tried the dbsastype syntax, but my query locked up. Specifying (compress=yes) worked perfectly. On a sample of obs=1000 the data set was compressed by over 96%. I'm downloading the entire data set down right now. I estimate that the file will be less than 50gb, which will be a huge improvement over what I would have ended up with. Thank you, SASKiwi! Best Bill

BillJones · ‎06-12-2014

Hello, I have a really large data set on a sql server that I'm trying to download. The issue I'm having is the variable lengths are huge. Most variables have a length of 255 when perhaps 8 would be appropriate. How do I reformat the variables and simultaneously download the data? I tried the code below but get the following error message: "ERROR: Describe error: IColumnsInfo::GetColumnInfo failed. : Deferred prepare could not be completed.: Statement(s) could not be prepared.: Incorrect syntax near 'format'." Code: proc sql; connect to oledb as codata (init_string="Provider=SQLOLEDB;Password=&pass.;Persist Security Info=True;User ID=&user.; Initial Catalog=misc;Data Source=Sales" schema=dbo); create table out.test as select * from connection to codata (select Idfield as Idfield format=10. from superlargetable ); disconnect from codata; quit; Note when I remove the format=10. the code works fine. Thank you very much for any suggestions! -Bill

BillJones · ‎06-09-2014

Ksharp, Thanks very much for the suggestion. PROC HTTP works perfectly. Regards, Bill

BillJones · ‎06-08-2014

Ok, I've determined that CURL is the best way to solve this issue and have installed it on my machine. Now I'm trying to run some test code. There are no errors in my log, see below. However, I can't seem to generate the data that I want. It appears that curl is working and a text file is created. Except the text file is blank. Does anyone have any suggestions on what I'm doing wrong? Thanks so much, Bill 87 dm 'clear log'; 88 89 %let loc=https://www.yahoo.com/; 90 data _null_; 91 infile %sysfunc(quote(curl -k &loc. > C:\Users\bjones\SAS\Data\data_temp.txt)) 91 ! pipe ; 92 input ; 93 put _infile_; 94 run; NOTE: The infile "curl -k https://www.yahoo.com/ > C:\Users\bjones\SAS\Data\data_temp.txt" is: Unnamed Pipe Access Device, PROCESS=curl -k https://www.yahoo.com/ > C:\Users\bjones\SAS\Data\data_temp.txt, RECFM=V,LRECL=256 NOTE: 0 records were read from the infile "curl -k https://www.yahoo.com/ > C:\Users\bjones\SAS\Data\data_temp.txt". NOTE: DATA statement used (Total process time): real time 0.16 seconds cpu time 0.01 seconds

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 FORMAT: How to make a library from an existing data set?

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

Re: SAS utilizing small percentage of system resources

Re: Merging two datasets with different but similar columns

Re: SAS utilizing small percentage of system resources

Re: SAS utilizing small percentage of system resources

Re: SAS utilizing small percentage of system resources

Re: SAS utilizing small percentage of system resources

Re: SAS utilizing small percentage of system resources

Re: SAS utilizing small percentage of system resources

SAS utilizing small percentage of system resources

Re: Using PROC SQL to reformat variables

Using PROC SQL to reformat variables

Re: ERROR: SSL error is "The token supplied to the function is invalid...

Re: ERROR: SSL error is "The token supplied to the function is invalid...