How to append a column with data to an existing dataset?

[crunching_data]

Hi there!

trying to append (merge, add, whatever) a column with data to an existing dataset. I am using .txt formats. I am doing this in a special context; A SAS program was written to link postal codes (Canada) to Census geography. The output is a .txt file containing multiple columns. 

Now what I want is to append a single column (4-digit info) to that .txt ouptput. Simple? not when you are new to SAS.

Many thanks. 

[ballardw]

---

@crunching_data wrote:

Hi there!

trying to append (merge, add, whatever) a column with data to an existing dataset. I am using .txt formats. I am doing this in a special context; A SAS program was written to link postal codes (Canada) to Census geography. The output is a .txt file containing multiple columns. 

Now what I want is to append a single column (4-digit info) to that .txt ouptput. Simple? not when you are new to SAS.

Many thanks. 

---

With SAS the generic approach would be to modify the dataset before it is written out to the txt file. I am not going to say that it can't be done directly with text files but the coding would likely be much more complex and fragile.

So, the first step is getting that additional information into a SAS data set.  Then combine before writing out the text file.

In SAS terms "merge" means to add "sideways" for data, usually on one or more key values: Example

data work.one;
   input id value1;
datalines;
1   123
2   345
3   567
;

data work.two;
   input id value2;
datalines;
1  999
2  888
4  333
;

data work.merged;
  merge work.one work.two;
  by id;
run;

note what happens when both ID values are not in both sets.

In SAS terms "append" means to stack vertically. Example with the same input data set:

data work.appended;
  set work.one work.two;
run;

So which of these actions looks closer to what you want? I would guess the merge but would have to see some sort of example data.

If you are adding additional rows then that would be an Append, and the variables with the same names will have limitations such as must be the same type and if character values of the same length.

If you share the code used to write out the text file, an example of your existing data (before combining) and an example of the data to be added we might be able to provide better details of what you need.

Data is best provided as a data step as above and pasted into a code box opened using the forum's {I} icon. Similarly copy existing code from the editor and paste into a code box.

Likely this is confusing but: Instructions here: https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat... will show how to turn an existing SAS data set into data step code that can be pasted into a forum code box using the {i} icon or attached as text to show exactly what you have and that we can test code against.

[crunching_data]

thanks for this, but unfortunately the code that generates the txt is 500+ lines, so I won't give a sample of it (unfortunately it is under a user license agreement also). Basically, there is 100 000 or so lines with multiple columns. The first few lines of the program let you choose the output destination and format (so far .txt was the only output format that was readable). Basically, the input of this long SAS program is a txt file where you specify the character position of the postal code, and the position of the characters for the unique ID number. 

ex: 

@ 6 PCODE $CHAR6. 

@ 8 ID $CHAR10.;         

Once this is specified, the SAS program reads in the .txt files containing the postal codes, and then adds all the variables needed (census geographies), that correspond to the postal code, into a .txt output (specified by me). Now what I would like to do, is to add to this .txt file a simple column (same number of records) that contains the year of the postal code. So basically I have that year data in the same order, the same amount of records, it just needs to merged to that .txt file. 

does this makes more sense?

thanks again, I'm new to this!                                 

[Tom · Accepted solution]

You need to answer the question of how do you know which value to add to line 100 versus line 200.

If you have a text file with 1,000 lines and a dataset with 1,000 observations and want to match them one for one and create a new text file the basic logic could look like this.

So let's assume the original file is named 'original_file.txt' and you want to make a new file named 'new_file.txt' that adds the data from the variable NEW_VARIABLE in the dataset named NEW_DATA.  If the file is a CSV file then the code could look like this:

data _null_;
  infile 'original_file.txt' ;
  file 'new_file.txt' ;
  input ;
  set new_data ;
  put _infile_ ',' new_variable ;
run;

If the file has values in fixed locations then the PUT statement just need to change to indicate where on the line the new_variable's value is to be written.  So perhaps the new value needs to start in column number 205.

  put _infile_ @205 new_variable ;