BookmarkSubscribeRSS Feed
niloo
Obsidian | Level 7

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

13 REPLIES 13
ballardw
Super User

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
Obsidian | Level 7

The first rows of the input are :

CalDtewretd
12/10/1999
12/13/19990.00339
12/14/1999-0.0061
12/15/1999-0.01023
12/16/19990.001209
12/17/19990.014306
12/20/19990.013212
12/21/1999-0.002
12/22/1999-0.00114
12/23/19990.009989
12/24/19990.009891
12/27/19990.007243
12/28/1999-0.0044
12/29/1999-0.01028
12/30/1999-0.00243
12/31/1999-0.02196
1/3/20000.00339
1/4/2000-0.0061
1/5/2000-0.01023
1/6/20000.001209
1/7/20000.014306
1/10/20000.013212
1/11/2000-0.002

Thanks,

Niloo

niloo
Obsidian | Level 7

The first rows of the input are:

CalDtewretd
12/10/1999
12/13/19990.00339
12/14/1999-0.0061
12/15/1999-0.01023
12/16/19990.001209
12/17/19990.014306
12/20/19990.013212
12/21/1999-0.002
12/22/1999-0.00114
12/23/19990.009989
12/24/19990.009891
12/27/19990.007243
12/28/1999-0.0044
12/29/1999-0.01028
12/30/1999-0.00243
12/31/1999-0.02196
1/3/20000.00339
1/4/2000-0.0061
1/5/2000-0.01023
1/6/20000.001209
1/7/20000.014306
1/10/20000.013212
1/11/2000-0.002

Thanks,

Niloo

PGStats
Opal | Level 21

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

PG
niloo
Obsidian | Level 7

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
Super User

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
Obsidian | Level 7

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
Obsidian | Level 7

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
Super User

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
Super User

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
Super User Tom
Super User

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
Obsidian | Level 7

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
Super User

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 13 replies
  • 6823 views
  • 3 likes
  • 6 in conversation