DATA Step, Macro, Functions and more

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

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 8
Accepted Solution

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

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.


Accepted Solutions
Solution
‎02-23-2016 01:22 AM
Valued Guide
Posts: 2,177

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

Posted in reply to james_alive
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

View solution in original post


All Replies
Super User
Posts: 5,437

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

Posted in reply to james_alive
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.
Data never sleeps
Occasional Contributor
Posts: 8

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

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.

Respected Advisor
Posts: 3,799

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

Posted in reply to james_alive

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?

Occasional Contributor
Posts: 8

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

Posted in reply to data_null__

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

filename1 - File is used to calculate the annual premium

Super User
Posts: 5,437

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

Posted in reply to james_alive
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.
Data never sleeps
Super User
Posts: 11,343

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

Posted in reply to james_alive

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.

Occasional Contributor
Posts: 8

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

Thanks alot for your suggestion. I just overlooked this fact of keeping a tab on variable length n all. Smiley Happy
Solution
‎02-23-2016 01:22 AM
Valued Guide
Posts: 2,177

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

Posted in reply to james_alive
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
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 8 replies
  • 353 views
  • 0 likes
  • 5 in conversation