How to Determine File Structure of .BCP file Using SAS

[smg3141]

I have 2 .BCP files I am trying to import into SAS. I used proc import, but the date variables changed format. I would like to figure out the length of each variable so I can import using inline. Is there a way to determine the lengths of the variables in the .BCP file?

[japelin]

PROC IMPORT cannot determine the length.

Access dictionary table to figure out the length of variables after import.

For example.

data _null_;
  set sashelp.vcolumn;
  where LIBNAME="SASHELP" and MEMNAME="CLASS";
  put libname memname name length;
run;

proc sql;
   select libname, memname, name, length  
   from DICTIONARY.COLUMNS
   where LIBNAME="SASHELP" and MEMNAME="CLASS";
quit;

[andreas_lds]

BCP? Never heard of it. Please explain.

If those files are text-files, you must have documentation of the structure, if not ask for one. Guessing types, lengths and informats is the best way to create more work in the long-run.

[Patrick]

From the Microsoft documentation https://docs.microsoft.com/en-us/sql/tools/bcp-utility?view=sql-server-ver15 

So I guess you would eventually want the format file as well. But then because you're not importing into a SQL server you would then also need to write a program that maps the SQL server data types and attributes to the appropriate SAS data types and attributes (like the right informat for any date and datetime values).

Ideally connect directly to the SQL server and export the data this way. Or then try to get the data as JSON files and not as .bcp

[Tom]

It really depends on what is in those files.

From the documentation page linked by an earlier reply you might be lucky and they are just simple tab delimited files.

-c
Performs the operation using a character data type. This option does not prompt for each field; it uses char as the storage type, without prefixes and with \t (tab character) as the field separator and \r\n (newline character) as the row terminator. -c is not compatible with -w.

LOOK at the file and see what you have.

Check to see if they are text files with lines by looking at the first 5 lines.

filename BCP 'myfilename.bcp';
data _null_;
  infile bcp obs=5;
  input;
  list;
run;

Or look at them a binary files.  To check the first 500 bytes you could use:

data _null_;
  infile bcp recfm=f lrecl=100 obs=5;
  input;
  list;
run;