Register table with Space delimited column names

[Mohit21]

Hi Everyone,

I am new to SAS DI and previously working on base sas.

The problem is I have to make a job which will use external file.

However, The external file contains spaces in column names.

For example:

Student ID,Nick Name etc....

It does not have quotes or underscore so the DI studio reading it as two different columns.

Could someone please let me know how to deal with this.

Regards,

Mohit

[Jagadishkatam]

Could you please let me know the format of the external file you are trying to read and the procedure you are using to read the external file in sas

[Mohit21]

It is CSV.

and i am importing using NEW->External FILE->DELIMITED.

[SASKiwi]

If you run your import using this statement:

options validvarname=v7;

it will convert your column names to valid SAS names including replacing spaces with underscores.

I recommend you use this option as it will avoid having to write SAS code like this: 'My Non-Standard Column Name'n every time you reference non-standard names. 

[Patrick]

If you're new to DIS and haven't used another metadata driven ETL tool before then you will need some time to get your head around to grasp the difference between metadata and SAS code.

The DIS wizard you're using helps you to create External File Metadata. Even if the Wizard wouldn't treat blank separated column headings as two columns, you certainly wouldn't want to end-up with blank separated column names.

What you can do: Take a copy of your .csv with the headings and a few lines of data, then change the headings to the names you need as SAS variable names (compliant with SAS naming convention and your data model) and then use this file with the wizard. 

Alternatively and even easier: You know your data. Define the columns for the External File Metadata manually. The names don't need to be the same than what's in the .csv as header but should be what makes sense as SAS variable names. This being a repetitive ETL process you also don't want the wizard to define the column attributes like the length. You need in any case to amend what the wizard creates here as this is only appropriate for the .csv you currently have. Next day's .csv could contain longer strings.