The PROC CONTENTS output above displays Sortedby PERMNO DATE. It seems WHERE must be accompanied by BY to preserve the sorting status. Thanks. ... View more

The PROC CONTENTS output right above says crsp.msf is sorted (Sortedby PERMNO DATE). It seems (1) though crsp.msf per se is sorted, crsp.msf with WHERE is not, and (2) though crsp.msf with WHERE is not sorted, crsp.msf with both WHERE and BY is sorted. Thanks. ... View more

I got the information from Details in Properties in Explorer. The SAS System The CONTENTS Procedure Data Set Name CRSP.MSF Observations 4509846 Member Type DATA Variables 21 Engine V9 Indexes 5 Created 01/31/2019 15:39:40 Observation Length 168 Last Modified 01/31/2019 15:39:48 Deleted Observations 0 Protection Compressed NO Data Set Type Sorted YES Label Monthly Stock - Securities Data Representation SOLARIS_X86_64, LINUX_X86_64, ALPHA_TRU64, LINUX_IA64 Encoding latin1 Western (ISO) Engine/Host Dependent Information Data Set Page Size 65536 Number of Data Set Pages 11595 First Data Page 1 Max Obs per Page 389 Obs in First Data Page 361 Index File Page Size 8192 Number of Index File Pages 40018 Number of Data Set Repairs 0 Filename /wrds/crsp/sasdata/a_stock/msf.sas7bdat Release Created 9.0401M5 Host Created Linux Inode Number 2465424092 Access Permission rw-r----- Owner Name wrdsadmn File Size 725MB File Size (bytes) 759955456 Alphabetic List of Variables and Attributes # Variable Type Len Format Informat Label 18 ALTPRC Num 8 12.5 12.5 Price Alternate 20 ALTPRCDT Num 8 YYMMDDN8. YYMMDD6. Alternate Price Date 14 ASK Num 8 11.5 11.5 Ask 9 ASKHI Num 8 12.5 12.5 Ask or High Price 13 BID Num 8 11.5 11.5 Bid 8 BIDLO Num 8 12.5 12.5 Bid or Low Price 16 CFACPR Num 8 Cumulative Factor to Adjust Prices 17 CFACSHR Num 8 Cumulative Factor to Adjust Shares/Vol 1 CUSIP Char 8 8. 8. CUSIP Header 7 DATE Num 8 YYMMDDN8. Date of Observation 5 HEXCD Num 8 2. 2. Exchange Code Header 6 HSICCD Num 8 8. 8. Standard Industrial Classification Code 4 ISSUNO Num 8 8. 8. Nasdaq Issue Number 3 PERMCO Num 8 8. 8. PERMCO 2 PERMNO Num 8 8. 8. PERMNO 10 PRC Num 8 12.5 12.5 Price or Bid/Ask Average 12 RET Num 8 11.6 11.6 Returns 21 RETX Num 8 11.6 11.6 Returns without Dividends 15 SHROUT Num 8 Shares Outstanding 19 SPREAD Num 8 11.5 11.5 Spread Between Bid and Ask 11 VOL Num 8 10. 10. Volume Alphabetic List of Indexes and Attributes # of Unique # Index Values 1 CUSIP 32985 2 DATE 1117 3 HSICCD 1445 4 PERMCO 29481 5 PERMNO 32985 Sort Information Sortedby PERMNO DATE Validated YES Character Set ASCII Sort Option NODUPKEY Here I attach the results from PROC CONTENTS too. ... View more

It seems EXPAND by default (METHOD=SPLINE) alters the missing as 0.07 and then compute cumulative. METHOD=NONE prevents this. ... View more

The sorting of the original data is correct by PERMNO and DATE. Here I attach the code and the screenshots. rsubmit; data msf; set crsp.msf; by permno date;/*<-this works correctly*/ run; data msf1;/*<-the resulting msf1 is not sorted*/ set crsp.msf; where date<"1jan1930"d; run; data msf2;/*<-the resulting msf2 is sorted*/ set crsp.msf; where date<"1jan1930"d; by permno date; run; endrsubmit; The code above creates three data sets—MSF, MSF1, and MSF2. The BY in the first DATA has no problem. The only difference between MSF1 and MSF2 is the BY statement in the third DATA. 3 rsubmit; NOTE: Remote submit to WRDS commencing. 1 data msf; 2 set crsp.msf; 3 by permno date;/*<-this works correctly*/ 4 run; NOTE: There were 4509846 observations read from the data set CRSP.MSF. NOTE: The data set WORK.MSF has 4509846 observations and 21 variables. NOTE: DATA statement used (Total process time): real time 1.61 seconds cpu time 1.52 seconds 5 data msf1;/*<-the resulting msf1 is not sorted*/ 6 set crsp.msf; 7 where date<"1jan1930"d; 8 run; NOTE: There were 29968 observations read from the data set CRSP.MSF. WHERE date<'01JAN1930'D; NOTE: The data set WORK.MSF1 has 29968 observations and 21 variables. NOTE: DATA statement used (Total process time): real time 0.68 seconds cpu time 0.43 seconds 9 data msf2;/*<-the resulting msf2 is sorted*/ 10 set crsp.msf; 11 where date<"1jan1930"d; 12 by permno date; 13 run; NOTE: There were 29968 observations read from the data set CRSP.MSF. WHERE date<'01JAN1930'D; NOTE: The data set WORK.MSF2 has 29968 observations and 21 variables. NOTE: DATA statement used (Total process time): real time 1.00 seconds cpu time 0.44 seconds NOTE: Remote submit to WRDS complete. The following is the resulting (not original) MSF. The observations are sorted correctly by PERMNO and DATE. The following is MSF1 without BY. The PERMNO-DATE sorting disappears. The following is MSF2 with BY. The PERMNO-DATE sorting is correct. It seems SAS does not access to the original data sequentially if not BY. ... View more

It seems crsp.msf is sorted by PERMNO and DATE. I wonder whether WHERE affects the sorting status. ... View more

In other cases, I use format TIME yymmddn8.; to format the observations or TIME=intnx("month",TIME,0,"e"); to transform 1900-01-01 to 1900-01-31. I am using this unusual way due to a different reason only for this time. Thanks. ... View more

I know I can use format TIME yymmddn8.; but I am using this unusual way due to a different reason (senior coworker) for this time only. ... View more

If the INFILE is the troublemaker, then shouldn't it be the same for other cases? But this code produces no problem when it just reads PCES rather than PECND as follows. filename PCES "%sysfunc(getoption(work))\PCES.csv"; proc http method="get" out=PCES url="https://fred.stlouisfed.org/graph/fredgraph.csv?id=PCES"; run; data PCES; infile PCES; input TIME yymmdd10. +1 PCES; TIME=put(TIME,yymmddn8.)+0; run; Both PCES and PCEND are structurally identical as follows. DATE,PCES 1959-01-01,138.7 1959-02-01,140.0 1959-03-01,140.5 1959-04-01,141.5 1959-05-01,142.8 1959-06-01,144.3 1959-07-01,145.1 1959-08-01,146.1 1959-09-01,147.4 1959-10-01,148.3 1959-11-01,149.2 1959-12-01,150.4 The left and right are PCEND and PCES, respectively. SAS doesn't read the January in PCEND, but does read in PCES. Why are the results different? By the way, some FRED data are uneven—UMCSENT, for example, is less frequent than monthly before 1978, but becomes monthly in 1978. Also, date information in FRED such as 1950-01-01 actually means 1950-01-31 sometimes. So I rather used TIME here. ... View more

No note—this line changes the numeric value -365 to 19590101. ... View more

Thanks, but I attached some additional findings right above—when I just change the variable from PCEND to PCES, SAS just reads all the data in a correct way. Both data have an identical structure starting from the second. Then why are the results different here? ... View more

I understand that SAS cannot read the strings. However, shouldn't this be the same for other data too? The following code just alters PCEND by PCES. filename PCES "%sysfunc(getoption(work))\PCES.csv"; proc http method="get" out=PCES url="https://fred.stlouisfed.org/graph/fredgraph.csv?id=PCES"; run; data PCES; infile PCES; input TIME yymmdd10. +1 PCES; TIME=put(TIME,yymmddn8.)+0; run; This CSV file has the same structure. DATE,PCES 1959-01-01,138.7 1959-02-01,140.0 1959-03-01,140.5 1959-04-01,141.5 1959-05-01,142.8 1959-06-01,144.3 1959-07-01,145.1 1959-08-01,146.1 1959-09-01,147.4 1959-10-01,148.3 1959-11-01,149.2 1959-12-01,150.4 So if the previous problem is the strings in the first line, then the first line here shouldn't be recognized. Unlike the previous case, however, SAS correctly reads all the file from January even without the FIRSTOBS adjustment. What would be the difference here then? ... View more