data statement results in 0 observations

[niloo]

Hi,

I am trying to import a csv file into sas using an example:

/* Create the market index returns data set. This  */

/* example uses a SAS data step to read a CSV file,*/

/* but you can use any SAS method to create the    */

/* data set. The code below also checks the dates  */

/* to see whether they fall between a particular   */

/* date and today; if not, they must not have been */

/* read correctly. Check the PC SAS Log for a mess-*/

/* age generated by the data step. It is also best */

/* to view the SAS data set manually.              */

data MarketIndex; /* Change C:\Folder below as needed */

infile 'C:\Folder\NonCRSPStudy-Index.csv' end=_lastobs_

           dlm=',' truncover firstobs=2;

input CalDt:mmddyy10. VWRetD;

format CalDt yymmddn8.;

if not ('01JAN1925'D<CalDt<today()) then do;

  put 'ERROR: Check dates! Example: ' _n_= CalDt=yymmddn8.;

  stop;

end;

else if _lastobs_ then put 'Dates appear plausible.';

run;

I modified the statements( in green) to use them on my own data:

my file is located in evantus folder in my drive C

my index is equally weighted hence: ewretd

my dates start from 12/10/1999

data MarketIndex;

infile 'C:\evantus\MSCI.csv' end=_lastobs_

           dlm=',' truncover firstobs=2;

input CalDt:mmddyy10. ewretd;

format CalDt yymmddn8.;

if not ('10Dec1999'D<CalDt<today()) then do;

  put 'ERROR: Check dates! Example: ' _n_= CalDt=yymmddn8.;

  stop;

end;

else if _lastobs_ then put 'Dates appear plausible.';

run;

The log says:

6886  data MarketIndex;

6887   infile 'C:\evantus\MSCI.csv' end=_lastobs_

6888             dlm=',' truncover firstobs=2;

6889   input CalDt:mmddyy10. ewretd;

6890   format CalDt yymmddn8.;

6891   if not ('10Dec1999'D<CalDt<today()) then do;

6892    put 'ERROR: Check dates! Example: ' _n_= CalDt=yymmddn8.;

6893    stop;

6894   end;

6895   else if _lastobs_ then put 'Dates appear plausible.';

6896  run;

NOTE: The infile 'C:\evantus\MSCI.csv' is:

      Filename=C:\evantus\MSCI.csv,

      RECFM=V,LRECL=32767,File Size (bytes)=87520,

      Last Modified=03Aug2015:09:19:06,

      Create Time=03Aug2015:09:19:18

NOTE: 0 records were read from the infile 'C:\evantus\MSCI.csv'.

NOTE: The data set WORK.MARKETINDEX has 0 observations and 2 variables.

NOTE: DATA statement used (Total process time):

      real time           0.00 seconds

      cpu time            0.00 seconds

NOTE: This SAS session is using a registry in WORK.  All changes will be lost at the end of

      this session.

And the created MarketIndex file has 0 observations.

Why can't my sas read the observations?

Thanks,

Niloo

[ballardw]

Can you post a few lines of the input data set?

The fact that you neither of your diagnostic messages were Put to the log looks a bit suspicious.

[niloo]

The first rows of the input are :

CalDt	ewretd
12/10/1999
12/13/1999	0.00339
12/14/1999	-0.0061
12/15/1999	-0.01023
12/16/1999	0.001209
12/17/1999	0.014306
12/20/1999	0.013212
12/21/1999	-0.002
12/22/1999	-0.00114
12/23/1999	0.009989
12/24/1999	0.009891
12/27/1999	0.007243
12/28/1999	-0.0044
12/29/1999	-0.01028
12/30/1999	-0.00243
12/31/1999	-0.02196
1/3/2000	0.00339
1/4/2000	-0.0061
1/5/2000	-0.01023
1/6/2000	0.001209
1/7/2000	0.014306
1/10/2000	0.013212
1/11/2000	-0.002

Thanks,

Niloo

[niloo]

The first rows of the input are:

CalDt	ewretd
12/10/1999
12/13/1999	0.00339
12/14/1999	-0.0061
12/15/1999	-0.01023
12/16/1999	0.001209
12/17/1999	0.014306
12/20/1999	0.013212
12/21/1999	-0.002
12/22/1999	-0.00114
12/23/1999	0.009989
12/24/1999	0.009891
12/27/1999	0.007243
12/28/1999	-0.0044
12/29/1999	-0.01028
12/30/1999	-0.00243
12/31/1999	-0.02196
1/3/2000	0.00339
1/4/2000	-0.0061
1/5/2000	-0.01023
1/6/2000	0.001209
1/7/2000	0.014306
1/10/2000	0.013212
1/11/2000	-0.002

Thanks,

Niloo

[PGStats]

Your data is not CSV. CSV means comma separated values. Start by removing the DLM= option.

[niloo]

PGStats,

Thank you for your response. I saved my excel file as csv . The file's name is  MSCI.csv. I thought it was csv. How can I save an excel file as csv then?

Even after deleting the DLM= option there are zero observations in the results.

Log:

6897  data MarketIndex;

6898   infile 'C:\evantus\MSCI.csv' end=_lastobs_

6899              truncover firstobs=2;

6900   input CalDt:mmddyy10. ewretd;

6901   format CalDt yymmddn8.;

6902   if not ('10Dec1999'D<CalDt<today()) then do;

6903    put 'ERROR: Check dates! Example: ' _n_= CalDt=yymmddn8.;

6904    stop;

6905   end;

6906   else if _lastobs_ then put 'Dates appear plausible.';

6907  run;

NOTE: The infile 'C:\evantus\MSCI.csv' is:

      Filename=C:\evantus\MSCI.csv,

      RECFM=V,LRECL=32767,File Size (bytes)=87520,

      Last Modified=03Aug2015:09:19:06,

      Create Time=03Aug2015:09:19:18

NOTE: 0 records were read from the infile 'C:\evantus\MSCI.csv'.

NOTE: The data set WORK.MARKETINDEX has 0 observations and 2 variables.

NOTE: DATA statement used (Total process time):

      real time           0.00 seconds

      cpu time            0.01 seconds

NOTE: This SAS session is using a registry in WORK.  All changes will be lost at the end of

      this session.

Thanks,

Niloo

[Reeza]

That means the sample you posted wasn't from your CSV file. Open your file in a text editor i.e. notepad/wordpad/notepad++ and see what the data looks like.

Have you tried a standard proc import, did that work?

[niloo]

Oh Ok, this is how it looks like in notepad:

CalDt,ewretd

12/10/1999,

12/13/1999,0.003389725

12/14/1999,-0.006097406

12/15/1999,-0.010225398

12/16/1999,0.001208791

12/17/1999,0.014305982

12/20/1999,0.01321201

12/21/1999,-0.001999569

12/22/1999,-0.001136617

12/23/1999,0.009989411

12/24/1999,0.00989061

No I have not used the proc import yet. let me try that as well.

Thanks,

Niloo

[niloo]

I could not use the proc import as well , it is asking me to assign a library name but I do not know how. So I used the file>Import Data and it worked. But I still don't know why my data step did not work.

proc import out=MSCI.csv

datafile= "C:\evantus\MSCI.csv" dbms=csv replace;

getnames=yes;

datarow= 2;

GUESSINGROWS=20000;

run;

log:

6981  proc import out=MSCI.csv

6982  datafile= "C:\evantus\MSCI.csv" dbms=csv replace;

6983  getnames=yes;

6984  datarow= 2;

6985  GUESSINGROWS=20000;

6986  run;

ERROR: Library name is not assigned.

NOTE: The SAS System stopped processing this step because of errors.

NOTE: PROCEDURE IMPORT used (Total process time):

      real time           0.00 seconds

      cpu time            0.00 seconds

Thanks,

Niloofar

[ballardw]

This

proc import out=MSCI.csv

is generating the error. MSCI.CSV as the OUT destination says to put the dataset named CSV in the Library named MSCI.  And you don't have that library.

Basic program code to assign is a library is:

Library name "path"; where name is limited to 8 characters and the Path is valid for your operating system.

I recommend always using a full path like : "C:\folder\subfolder";

[Reeza]

Your questions seem to be of a very basic nature. You may want to consider some tutorials from the UCLA SAS site or the intro course provided by SAS.

SAS Starter Kit: Learning Modules

SAS Training in the United States -- SAS Programming 1: Essentials

[Tom]

From your log the csv file is empty.  The data step is stopping as soon at it runs the INPUT statement and does not execute any other statements.

[niloo]

But it is not empty, here are the first rows of my input file:

CalDt,ewretd

12/10/1999,

12/13/1999,0.003389725

12/14/1999,-0.006097406

12/15/1999,-0.010225398

12/16/1999,0.001208791

12/17/1999,0.014305982

12/20/1999,0.01321201

12/21/1999,-0.001999569

12/22/1999,-0.001136617

Thanks,

Niloo

[Kurt_Bremser]

Your log starts with lines above 6000, which indicates that there has been a lot going on in the current SAS session.

Terminate the session, start a new one, and then run your code snippet. You may have something like maxobs = 0 set from a previous code piece. So also look at your SAS options by running proc options, if the problem persists.

Your text editor may not display the whole truth about the infile, have a look at it with something like PSPad to display the file in hex, you may have some characters in there that confuse SAS.