topic How to Determine File Structure of .BCP file Using SAS in SAS Programming

How to Determine File Structure of .BCP file Using SAS

smg3141 — Thu, 12 May 2022 18:37:02 GMT

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?

Re: How to Determine File Structure of .BCP file Using SAS

japelin — Fri, 13 May 2022 04:22:18 GMT

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;

Re: How to Determine File Structure of .BCP file Using SAS

andreas_lds — Fri, 13 May 2022 06:02:18 GMT

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.

Re: How to Determine File Structure of .BCP file Using SAS

Patrick — Fri, 13 May 2022 09:40:14 GMT

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

Re: How to Determine File Structure of .BCP file Using SAS

Tom — Fri, 13 May 2022 15:13:49 GMT

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;