topic Re: How to get column names from an external excel file to an existing data set? in SAS Programming

How to get column names from an external excel file to an existing data set?

james_alive — Fri, 12 Feb 2016 03:29:51 GMT

I have an existing data set prepared from an external csv file(file1) (csv file has no headings), with column names as var1, var2, var3, so on.

For the column names, I have a different excel file (file2), can anybody help me with how to get the column names from file2 into existing dataset (created from file1).

PS: New to SAS env. Any help will be highly appreciated.

Re: How to get column names from an external excel file to an existing data set?

LinusH — Fri, 12 Feb 2016 06:28:25 GMT

The easiest is usually to get the names during import, from the header of the file. Perhaps if you could save your Excel as csv, and then import them in one go.
Similar option is to import the Excel file, and the use SQL INSERT INTO (just be 100% sure of the variable order).
Otherwise, you may need to build some kind of macro logic.

Re: How to get column names from an external excel file to an existing data set?

james_alive — Fri, 12 Feb 2016 06:35:24 GMT

Thanks for the prompt response but however I have 29 data files that are not having header.

the 30th file is having headers for all the above 29 data files and header details are listed in the below format:

FileName - Table1

Field11 Description11

Field12 Description12

FileName - Table2

Field21 Description21

Field22 Description22

I hope you understand this.

The only solution that I can think of is renaming the variables while preparing the dataset. Otherthan this I have no other clue.

Please see if any sample macro you can provide which caters this scenario.

Re: How to get column names from an external excel file to an existing data set?

LinusH — Fri, 12 Feb 2016 07:15:50 GMT

What I see is a data step that reads your variable name data set. Based on the values in that, use call execute to either directly issue proc datasets code, or call a macro with parameters. And then the macro have the proc datasets logic. These methods are equal, but calling a macro is imo more readible.

Re: How to get column names from an external excel file to an existing data set?

data_null__ — Fri, 12 Feb 2016 09:08:13 GMT

@james_alive wrote:

Thanks for the prompt response but however I have 29 data files that are not having header.

the 30th file is having headers for all the above 29 data files and header details are listed in the below format:

FileName - Table1

Field11 Description11

Field12 Description12

FileName - Table2

Field21 Description21

Field22 Description22

I hope you understand this.

The only solution that I can think of is renaming the variables while preparing the dataset. Otherthan this I have no other clue.

Please see if any sample macro you can provide which caters this scenario.

Does Description include useful information like data type and informat?

Re: How to get column names from an external excel file to an existing data set?

ballardw — Fri, 12 Feb 2016 16:39:39 GMT

A very similar topic is in this thread

https://communities.sas.com/t5/Base-SAS-Programming/Provided-Headers-vs-Provided-Dataset-without-Headers/m-p/249533

it may contain some helpful hints.

Also, if you separately proc import 30 datasets that should be the same you face a very real possibility of having some variables as characte in some sets and numeric in others, or the lengths of character variables being incompatible. So you may have more work ahead than just renaming columns, especially if your next step involved combining any of these sets.

Re: How to get column names from an external excel file to an existing data set?

james_alive — Sun, 21 Feb 2016 08:06:52 GMT

Description here means, the descriptive text for file. for eg,

filename1 - File is used to calculate the annual premium

Re: How to get column names from an external excel file to an existing data set?

Peter_C — Sun, 21 Feb 2016 22:32:05 GMT

You could write the required code to read all the data files, in a data step that reads the column names file.
Unless that colunn names file provides column data types ( like date format/ string length/ or numeric) your generated code would need to read all as strings.
It should be possible

Re: How to get column names from an external excel file to an existing data set?

james_alive — Tue, 23 Feb 2016 06:23:13 GMT

Thanks alot for your suggestion. I just overlooked this fact of keeping a tab on variable length n all. 🙂