Mixed=yes function

Reply
Contributor
Posts: 20

Mixed=yes function

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;

Super Contributor
Posts: 644

Re: Mixed=yes function

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

Super User
Posts: 10,500

Re: Mixed=yes function

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.

Super Contributor
Posts: 644

Re: Mixed=yes function

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

Richard

Ask a Question
Discussion stats
  • 3 replies
  • 3309 views
  • 0 likes
  • 3 in conversation