BookmarkSubscribeRSS Feed
AnaV
Calcite | Level 5

Hi. I encountered a problem when I imported my data from .csv to SAS. One of my columns contains cusips. Some cusips are purely numeric while others contain some letters. For example:

Cusips

3783310

04033V10

When I try to import the data into sas I get the message saying that some cusip values are invalid. My file is very large with many variables and rows. When I try to run guessingrows it takes a very long time to run each dataset. I tried using mixed=yes, but I always get an error message saying it is out of proper order.

Here is my code:

PROC IMPORT OUT= WORK.INSTOWN2000Q1

            DATAFILE= "F:\SAS Institutional Ownership\46_q1_2000.csv"

            DBMS=CSV REPLACE;

     GETNAMES=YES;

     DATAROW=2;

RUN;

3 REPLIES 3
RichardinOz
Quartz | Level 8

Assuming your environment is SAS on Windows, here is what you do:

  • Run Proc Import but ignore the output
  • Open a new program window
  • Press F4 to retrieve the generated SAS code
  • Examine the Informat statement for the cusips
  • Change the Informat statement for cusips to use informat $char32.
  • Change the Format statement for cusips to use format $char32.
  • Change the Input statment to add $ after cusips
  • Replace proc import in your original program with the amended data step code
  • Rerun

Richard

ballardw
Super User

From your file name I suspect you are doing this on some sort of quaraterly basis. I would try to make this program useable for the next quarter by working with the data source to make sure all my variable lengths are long enough to take any data sent later. There's nothing liking having to keep modifying programs to add 4 characters here and 10 there or change a character to numeric because my first data set didn't have any values for an occasionally used field as the data content changes.

Also a good idea to examine variable names generated as some data sources may give you column headings that generate SAS variable names you don't like or are longer than you really want to use. So you can use this opportunity to make short variables and assign labels that document and make them easier to use.

RichardinOz
Quartz | Level 8

Also, note that the Mixed option is not available for csv files but can be specified for Excel libnames.

Richard

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!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

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
  • 3 replies
  • 5711 views
  • 0 likes
  • 3 in conversation