topic Re: data statement results in 0 observations in SAS Procedures

data statement results in 0 observations

niloo — Mon, 03 Aug 2015 17:22:31 GMT

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

Re: data statement results in 0 observations

ballardw — Mon, 03 Aug 2015 17:58:03 GMT

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.

Re: data statement results in 0 observations

niloo — Mon, 03 Aug 2015 18:20:12 GMT

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

Re: data statement results in 0 observations

niloo — Mon, 03 Aug 2015 18:22:55 GMT

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

Re: data statement results in 0 observations

PGStats — Mon, 03 Aug 2015 18:34:30 GMT

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

Re: data statement results in 0 observations

niloo — Mon, 03 Aug 2015 18:43:18 GMT

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

Re: data statement results in 0 observations

Reeza — Mon, 03 Aug 2015 19:21:20 GMT

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?

Re: data statement results in 0 observations

niloo — Mon, 03 Aug 2015 19:28:41 GMT

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

Re: data statement results in 0 observations

Tom — Mon, 03 Aug 2015 19:47:16 GMT

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.

Re: data statement results in 0 observations

niloo — Mon, 03 Aug 2015 19:49:33 GMT

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

Re: data statement results in 0 observations

niloo — Mon, 03 Aug 2015 20:12:04 GMT

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

Re: data statement results in 0 observations

ballardw — Mon, 03 Aug 2015 23:15:06 GMT

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";

Re: data statement results in 0 observations

Reeza — Mon, 03 Aug 2015 23:34:50 GMT

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

Re: data statement results in 0 observations

Kurt_Bremser — Tue, 04 Aug 2015 06:42:35 GMT

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.