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

[james_alive]

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.

[LinusH]

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.

[james_alive]

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.

[data_null__]

---

@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?

[james_alive]

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

filename1 - File is used to calculate the annual premium

[LinusH]

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.

[ballardw]

A very similar topic is in this thread

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

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.

[james_alive]

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

[Peter_C · Accepted solution]

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