How to get TYPE, LENGTH, LABEL, FORMAT, INFORMAT, and INPUT using SAS Enterprise

[Jallah_Kennedy]

Please help me with the syntax or process to use with SAS Enterprise to automatically obtain the following from large csv or Excel dataset:

TYPE

LENGTH

LABEL

FORMAT

INFORMAT

INPUT

A former colleague used SAS Enterprise to often provide these for large csv and Excel datasets to avoid us spending so much time counting and typing (e.g. length and label….) for many variables.

If there is a syntax that can also be used with Base SAS 9.4, that will be even more helpful.

Thanks.

[Quentin]

Can you explain your question a bit more?

Type, Length, Label, etc are attributes of a SAS dataset.  You can't get them from a CSV or Excel file, without reading the data into a SAS dataset first.

Is your question "how can I read data from a CSV file or Excel file without typing in a list of variables and informats?"

If it is that question, you can take a look at PROC IMPORT, which will try to guess column types, and the XLSX engine.

[Jallah_Kennedy]

Thanks so much, Quentin for the prompt response.

No, that's not what I am looking for. 

I think there is a function or some way to have SAS Enterprise to provide an entire output of the LENGTH, LABEL, FORMAT, INFORMAT, and INPUT. You can then use that to import to read the entire csv or xlsx file correctly.

This is because, sometimes when the variables are many variables and their names are longer, this process reduces the time and avoids some truncating while remaining within the SAS limits for the name of a variable.

[Tom]

Unless there were the creator of the file and so knew what went into it the only way to DERIVE those from the file is to look at the file and make guesses.

PROC IMPORT will do this.  For delimited text file, like a CSV file, you might also want to look at the %CSV2DS() macro.

If you have a SAS dataset you can get the NAME, TYPE, LENGTH of a variable using PROC CONTENTS.   That will also provide FORMAT, INFORMAT and LABEL (if any is attached).  It will also produce VARNUM to let you know the order in the SAS dataset.

Example:

proc contents data=have out=want noprint; run;
proc print data=want;
  var varnum name type length format informat label ;
run;

[Jallah_Kennedy]

Thanks so much, Tom.

I ran proc contents but did not add some of what you suggested. I will try them as well. Much appreciated.

[Kurt_Bremser]

You read the documentation of the csv file and write the code accordingly. Documentation of a data file has to contain all the information you need (type, length, format) to write the DATA step that reads the file.

Without documentation, either the SAS system (PROC IMPORT) or you have to make guesses, where usually your guesses will be better.

With consistent and proper documentation, you might be able to write code which creates the import code with reliable and consistent results; but you have to decide if the effort of writing such code (which will be substantial) is worth it.

[Jallah_Kennedy]

Thanks so much, Kurt.

I will try that. My initial attempts have not worked well but I will keep trying. One other plan is to rename the variables to shorten the lengths because some are being truncated.

I appreciate your time and guidance.