BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
james_alive
Calcite | Level 5

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Peter_C
Rhodochrosite | Level 12
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

8 REPLIES 8
LinusH
Tourmaline | Level 20
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
james_alive
Calcite | Level 5

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__
Jade | Level 19

@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
Calcite | Level 5

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

filename1 - File is used to calculate the annual premium

LinusH
Tourmaline | Level 20
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
ballardw
Super User

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
Calcite | Level 5
Thanks alot for your suggestion. I just overlooked this fact of keeping a tab on variable length n all. 🙂
Peter_C
Rhodochrosite | Level 12
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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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