topic Re: Mixed=yes function in SAS Programming

Mixed=yes function

AnaV — Tue, 19 Feb 2013 22:27:51 GMT

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;

Re: Mixed=yes function

RichardinOz — Wed, 20 Feb 2013 09:44:31 GMT

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

Re: Mixed=yes function

RichardinOz — Thu, 21 Feb 2013 04:00:56 GMT

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

Richard

Re: Mixed=yes function

ballardw — Thu, 21 Feb 2013 16:54:24 GMT

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.