About Jacob3

Jacob3 · ‎07-22-2022

I am trying to download a CSV file directly from a website. The file is about 1.5 GB (roughly 5.5 million rows). This works for 1 GB which corresponds to 3,600,716 observations. I then tried just downloading the file from the website directly to my computer. It keeps restarting the download at exactly 1 GB. Any ideas on how I can pull the full file? I use the following code: filename out "c:\temp\ratings.csv"; proc http url="http://ratingshistory.info/csv/20220601%20Standard%20&%20Poor's%20Ratings%20Services%20US%20Public.csv" method="get" out=out; run; The log is: ERROR: Request Failed and number of retries has been exceeded. NOTE: PROCEDURE HTTP used (Total process time): real time 4:02.51 cpu time 22.67 seconds NOTE: 200 OK NOTE: The SAS System stopped processing this step because of errors.

Jacob3 · ‎05-27-2022

@Reeza , I should've come up with this myself :(. Thank you!

Jacob3 · ‎05-27-2022

Suppose I have the following data (in reality there are many more observations & the Z1, Z2, etc variables go to Z1000): data have; input Obs:32 ID:$8. Z1:32. Z2:32. Z3:32.; datalines; 1 A 5 5 5 2 B 7.5 7.5 7.5 3 C 6.123 6.123 6.123 4 D 8 8 8 5 E 1 1 1 ; run; The only thing I want to do is randomly sort each Z column, but importantly all of these sorts have to be independent of each other. An example of the ending dataset I would want: 1 A 8 1 6.123 2 B 6.123 7.5 5 3 C 7.5 5 1 4 D 5 6.123 8 5 E 1 8 7.5 If there were in fact only 3 'Z' columns I would just do the below: data have2; set have (keep = Z1); num = rand('normal',0,1); run; Then sort on num, create a count variable _N_ which I will use to left join back to the obs variable, then repeat twice more. However, given I have thousands of columns, this brute force method is not appealing. I am probably overlooking something. Any ideas?

Jacob3 · ‎01-17-2020

It is intermittent, it has only started today. I am able to export a simple dataset to STATA, but for whatever reason, not the one I would like to. I use SAS/STATA through a remote connection, I do not have authorization to write a file to the C: drive.

Jacob3 · ‎01-17-2020

I have exported data from SAS to STATA hundreds of times with no problems. Recently, I keep getting the error: "Invalid Operation" "Termination due to Floating Point Exception". I have looked online on how to remedy this, but have not had success. I would appreciate any advice to fix this. I am copying in below the relevant part of my log file. NOTE: Table MUNI.WSJ_EVERYTHINGBTRADEZTW created, with 95404 rows and 73 columns. 79 quit; NOTE: PROCEDURE SQL used (Total process time): real time 0.76 seconds cpu time 0.09 seconds 80 81 82 Proc export DATA=muni.WSJ_everythingBtradeztw DBMS=STATA 83 OUTfile='H:\SASdata\Bank\muni\vol_1trades_all_issuers_30days_wsjBtw' 84 replace; 85 Run; ERROR: Invalid Operation. ERROR: Termination due to Floating Point Exception NOTE: The SAS System stopped processing this step because of errors. NOTE: There were 1 observations read from the data set MUNI.WSJ_EVERYTHINGBTRADEZTW. NOTE: PROCEDURE EXPORT used (Total process time): real time 0.06 seconds cpu time 0.01 seconds

Jacob3 · ‎08-01-2019

novinosrin, It took about an hour to run, but....mission accomplished. Thank you so much for your help!

Jacob3 · ‎08-01-2019

I kept the first 25 observations from my dataset and read it into sas inline. The below does not work. data have; input obs ANALYS year2 quarter2 est_date3 value1 value2 permno1 username1; cards; 1 073367 2017 1 20799 0.090 . 24643 . 2 076852 2017 1 20807 0.380 . 24643 . 3 114449 2017 1 20808 0.456 . 24643 . 4 073867 2017 1 20808 -0.170 . 24643 . 5 130621 2017 1 20823 0.370 . 24643 . 6 125794 2017 1 20824 0.030 . 24643 . 7 115965 2017 1 20828 0.040 . 24643 . 8 114449 2017 1 20835 0.211 . 24643 . 9 109116 2017 1 20836 0.300 . 24643 . 10 125794 2017 1 20837 0.210 . 24643 . 11 073367 2017 1 20837 -0.120 . 24643 . 12 073367 2017 1 20837 -0.090 . 24643 . 13 073867 2017 1 20838 0.190 . 24643 . 14 . 2017 1 20842 . 0.20 24643 372497 15 . 2017 1 20842 . 0.19 24643 379066 16 . 2017 1 20842 . 0.20 24643 51442 17 . 2017 1 20843 . 0.19 24643 368592 18 . 2017 1 20843 . 0.20 24643 372497 19 . 2017 1 20843 . 0.22 24643 68250 20 109116 2017 2 20843 0.360 . 24643 . 21 115965 2017 2 20844 0.300 . 24643 . 22 073867 2017 2 20844 0.840 . 24643 . 23 130621 2017 2 20846 0.540 . 24643 . 24 125794 2017 2 20849 0.460 . 24643 . 25 076852 2017 2 20850 0.230 . 24643 . ; run; proc contents data=have; run; data want2; array t (9999)_temporary_; array j(9999)_temporary_; do until(last.quarter2); set have; by permno1 year2 quarter2; if not missing(username1) then t(username1)=value2; if not missing(analys) then j(analys)=value1; avg1=mean(of t(*)); avg2=mean(of j(*)); output; end; call missing(of t(*),of j(*)); run; Below I am attaching my log file in case that helps. I am probably missing something silly. 71 72 data have; 73 input obs ANALYS year2 quarter2 est_date3 value1 value2 permno1 username1; 74 75 cards; NOTE: SAS went to a new line when INPUT statement reached past the end of a line. NOTE: The data set WORK.HAVE has 25 observations and 9 variables. NOTE: DATA statement used (Total process time): real time 0.03 seconds cpu time 0.00 seconds 102 ; 103 run; 104 105 proc contents data=have; 106 run; NOTE: PROCEDURE CONTENTS used (Total process time): real time 0.07 seconds cpu time 0.01 seconds 107 108 109 data want2; 110 array t (9999)_temporary_; 111 array j(9999)_temporary_; 112 do until(last.quarter2); 113 set have; 114 by permno1 year2 quarter2; 115 if not missing(username1) then t(username1)=value2; 116 if not missing(analys) then j(analys)=value1; 117 avg1=mean(of t(*)); 118 avg2=mean(of j(*)); 119 120 output; 121 end; 122 call missing(of t(*),of j(*)); 123 124 run; ERROR: Array subscript out of range at line 116 column 29. last.quarter2=0 obs=1 ANALYS=73367 year2=2017 quarter2=1 est_date3=20799 value1=0.09 value2=. permno1=24643 username1=. FIRST.permno1=1 LAST.permno1=0 FIRST.year2=1 LAST.year2=0 FIRST.quarter2=1 avg1=. avg2=. _ERROR_=1 _N_=1 NOTE: The SAS System stopped processing this step because of errors. NOTE: There were 2 observations read from the data set WORK.HAVE. WARNING: The data set WORK.WANT2 may be incomplete. When this step was stopped there were 0 observations and 11 variables. NOTE: DATA statement used (Total process time): real time 0.04 seconds cpu time 0.03 seconds

Jacob3 · ‎08-01-2019

Thanks again. The only things I can discern that are different between my sample and "have" is that firmid and analystid2 are character variables in my dataset.

Jacob3 · ‎07-31-2019

Sorry to bother, but I keep getting "ERROR: Array subscript out of range". I can run the code 2 times back to back and it will give me different lines at which this error is occurring. Do you have any advice on how to remedy this?

Jacob3 · ‎07-31-2019

Thanks for your reply novinosrin! The real data is of the form: DATE Year Quarter FIRMID ANALYSTID ESTIMATE Average(want) ANALYSTID2 ESTIMATE2 Average2(want) 1/1/2015 2015 1 1 1 0.5 0.5 10 0.6 0.6 1/1/2015 2015 1 1 2 0.6 0.55 . . 0.6 1/4/2015 2015 1 1 3 0.7 0.6 12 0.8 0.7 1/10/2015 2015 1 1 2 0.8 0.666666667 13 1 0.8 1/12/2015 2015 1 1 3 0.9 0.733333333 10 0.9 0.9 1/12/2015 2015 1 1 1 1 0.9 13 0.9 0.866666667 4/1/2015 2015 2 1 1 0.5 0.5 10 0.6 0.6 4/1/2015 2015 2 1 2 0.6 0.55 . . 0.6 4/4/2015 2015 2 1 3 0.7 0.6 12 0.8 0.7 4/10/2015 2015 2 1 2 0.8 0.666666667 13 1 0.8 4/12/2015 2015 2 1 3 0.9 0.733333333 10 0.9 0.9 4/12/2015 2015 2 1 1 1 0.9 13 0.9 0.866666667 7/1/2015 2015 3 1 1 0.5 0.5 10 0.6 0.6 7/1/2015 2015 3 1 2 0.6 0.55 . . 0.6 7/4/2015 2015 3 1 3 0.7 0.6 12 0.8 0.7 7/10/2015 2015 3 1 2 0.8 0.666666667 13 1 0.8 7/12/2015 2015 3 1 3 0.9 0.733333333 10 0.9 0.9 7/12/2015 2015 3 1 1 1 0.9 13 0.9 0.866666667 10/1/2015 2015 4 1 1 0.5 0.5 10 0.6 0.6 10/1/2015 2015 4 1 2 0.6 0.55 . . 0.6 10/4/2015 2015 4 1 3 0.7 0.6 12 0.8 0.7 10/10/2015 2015 4 1 2 0.8 0.666666667 13 1 0.8 10/12/2015 2015 4 1 3 0.9 0.733333333 10 0.9 0.9 10/12/2015 2015 4 1 1 1 0.9 13 0.9 0.866666667 1/1/2016 2016 1 1 1 0.5 0.5 10 0.6 0.6 1/1/2016 2016 1 1 2 0.6 0.55 . . 0.6 1/4/2016 2016 1 1 3 0.7 0.6 12 0.8 0.7 1/10/2016 2016 1 1 2 0.8 0.666666667 13 1 0.8 1/12/2016 2016 1 1 3 0.9 0.733333333 10 0.9 0.9 1/12/2016 2016 1 1 1 1 0.9 13 0.9 0.866666667 1/1/2015 2015 1 2 1 0.5 0.5 10 0.6 0.6 1/1/2015 2015 1 2 2 0.6 0.55 . . 0.6 1/4/2015 2015 1 2 3 0.7 0.6 12 0.8 0.7 1/10/2015 2015 1 2 2 0.8 0.666666667 13 1 0.8 1/12/2015 2015 1 2 3 0.9 0.733333333 10 0.9 0.9 1/12/2015 2015 1 2 1 1 0.9 13 0.9 0.866666667 Where I want the average to be computed within year quarter and firm for two separate types of analysts (analystid & analystid2), while omitting past values of estimates from the moving average when the same analyst makes a new estimate.

Jacob3 · ‎07-31-2019

I have a dataset of analyst forecasts. I am wanting to produce a rolling average for each firm. Normally, this would be simple and I could accomplish using EXPAND or using an accumulator variable and dividing by the count. However, In my case an analyst can make more than one forecast in a period. If they do, I only want one of their forecasts to go into the rolling average at a time. I am copying below an example of the data. DATE FIRMID ANALYSTID ESTIMATE Average(want) 1/1/2015 1 1 0.5 0.5 1/1/2015 1 2 0.6 0.55 1/4/2015 1 3 0.7 0.6 1/10/2015 1 2 0.8 0.666666667 1/12/2015 1 3 0.9 0.733333333 1/12/2015 1 1 1 0.9 Up until (and including) 1/4/2015 it is a normal rolling average. However, starting with 1/10/2015 previous estimates drop out of the average. Specifically, the 1/1/2015 estimate by analyst 2 should no longer be included once the 1/10/2015 estimate by analyst 2 is now included. The data I copied in is just an example. In reality, it goes across many years, firms, and analysts. I would want the average to reset for each firm-quarter. However, if I can get the logic for my example data, I believe I can extend it to the broader dataset. I appreciate any help you have to offer.

Jacob3 · ‎06-25-2019

Thanks!

Jacob3 · ‎06-25-2019

I should be able to, but there can be hundreds of IDs under the header. So I could copy using the below if series = . then series = lag_series; if series = . & lag_series = . then series = lag2_series; etc. I am looking to get around this though.

Jacob3 · ‎06-25-2019

Thank you very much for your reply. I thought of doing something similar. However, only the first observation for each series contains the header information. So, as in the example data I provided, Type, Maturity, and Series are all missing for the subsequent observations after each "header observation".

Jacob3 · ‎06-25-2019

Hello, I am working with bond data. A single bond series has a header of descriptive information, then a bunch of cusips to identify individual bonds in the series. It resembles the below: Type Maturity Series ID GO 20 5000 280129 630693 RV 5 2400 273075 GO 7 2500 170564 GO 15 1000 321339 488161 379597 773084 RV 12 200 36892 659199 468958 I want the ID variable to be on the same row as the header information as below: Type Maturity Series ID ID2 ID3 ID4 GO 20 5000 280129 630693 RV 5 2400 273075 GO 7 2500 170564 GO 15 1000 321339 488161 379597 773084 RV 12 200 36892 659199 468958 There can be upwards of 100 separate IDs that all pertain to the same header information. I am struggling with how to do this and would appreciate any advice.

Online Status	Offline
Date Last Visited	‎07-29-2022 04:46 PM

Downloading a CSV using Proc HTTP

Re: How do you randomly and independently sort multiple columns?

How do you randomly and independently sort multiple columns?

Re: Trouble with Proc Export -- "Termination due to Floating Point Exc...

Trouble with Proc Export -- "Termination due to Floating Point Excepti...

Re: Rolling Average by firm and analyst

Re: Rolling Average by firm and analyst

Re: Rolling Average by firm and analyst

Re: Rolling Average by firm and analyst

Re: Rolling Average by firm and analyst

Re: How do you randomly and independently sort multiple columns?

Downloading a CSV using Proc HTTP

Re: How do you randomly and independently sort multiple columns?

How do you randomly and independently sort multiple columns?

Re: Trouble with Proc Export -- "Termination due to Floating Point Exc...

Trouble with Proc Export -- "Termination due to Floating Point Excepti...

Re: Rolling Average by firm and analyst

Re: Rolling Average by firm and analyst

Re: Rolling Average by firm and analyst

Re: Rolling Average by firm and analyst

Re: Rolling Average by firm and analyst

Rolling Average by firm and analyst

Re: How to collapse multiple observations into one

Re: How to collapse multiple observations into one

Re: How to collapse multiple observations into one

How to collapse multiple observations into one