topic Re: Importing and Merging Pipe Delimited files in New SAS User

Importing and Merging Pipe Delimited files

MoB — Wed, 28 Aug 2019 15:48:53 GMT

Hello,

I am trying to import two pipe delimited files, one that only contains the variable names, and the other containing the raw data. What would be the best way to import and merge these two files together. Both files are rather large ( about 2000 variables).

Re: Importing and Merging Pipe Delimited files

Reeza — Wed, 28 Aug 2019 16:02:05 GMT

Does the first file have the variable types? Or any other relevant information?
How do they currently look?

What do you want as final output? A lot of this depends on exactly how the file are laid out. The 'names' could be a single row of data or could be a column of data with variable types and lengths or other variations.

It really helps if you can show:
1 - What are you starting with?
2 - What do you want as output?
3 - Logic to get there
4 - Anything you've tried so far. Sometimes you're just off a little and sometimes you can be entirely off.

Re: Importing and Merging Pipe Delimited files

MoB — Wed, 28 Aug 2019 16:18:27 GMT

The names are listed in a single row of data, ie " ID|Location|Date...." The raw data is given in a similar structure.

For the final output I want the variable names listed in columns across the top and the observations listed below, just like they would be in a standard excel file.

My first thought was to import the raw data first with getnames=no, then rename the variables (this is before I realized there were roughly 2000 of them)

Next I thought I could import them both separately then merge them together somehow but then when I try to import the names file I get the following error message:

Unable to sample external file, no data in first 5 records.
ERROR: Import unsuccessful. See SAS Log for details.

Re: Importing and Merging Pipe Delimited files

Reeza — Wed, 28 Aug 2019 16:31:08 GMT

Read your data file with getnames=no or specify var1-var2000. I don't recommend PROC IMPORT with 2000 variables it will be wrong a lot of the time.

Read the names file into a file with

VariableOrder VariableName
1 ID
2 FirstName
3 MiddleInitial
4 LastName

Then you can create a data driven rename approach that is dynamic.

You won't be able to use a single macro variable but you can do it via proc datasets.

This link has the code on how to rename your data from a dataset that has a list of the old names and new names.
https://gist.github.com/statgeek/ba360dd1cf748bf6d0da7e2e16aafe66

Re: Importing and Merging Pipe Delimited files

MoB — Wed, 28 Aug 2019 16:55:14 GMT

Thanks! I'll give it a shot