Add column to existing dataset

Reply
Occasional Contributor
Posts: 9

Add column to existing dataset

Hi, 

 

I want to add a column (with observations) to an existing dataset. TEST1.xls has variables VOLS & VOIB and TEST2 has PROBS.

 

 

with equal no. of observations.  The output should have 3 variable VOLS VOlB PROBS with all observations in both datasets.

Even if no. of observations are unequal data from the columns should be added in the same sequence.

 

Thanks in advance,

 

Regards,

Dhir

Super User
Posts: 5,081

Re: Add column to existing dataset

SAS doesn't work on spreadsheets, except to import them into SAS data sets.  So assuming you imported your data into SAS data sets named TEST1 and TEST2, here are some possibilities.

 

This program discards extra observations from the "longer" data set (the one with extra rows):

 

data want;

set test1;

set test2;

run;

 

If I remember right, this program repeats the last observation on the "shorter" data set as many times as needed.  The number of observations depends on how many are in the "longer" data set:

 

data want;

merge test1 test2;

run;

 

You would have to test it to be sure.  But you might be looking for the first program anyway.  It's not clear what the result should be if one spreadsheet has more rows than the other.

Occasional Contributor
Posts: 9

Re: Add column to existing dataset

Thank you, both the programs worked. All the obs of "longer dataset" are added & corresponding cells are blank (.) 

 

Super User
Posts: 5,081

Re: Add column to existing dataset

Good to know.  Does one of these accomplish what you are asking for?

Super User
Super User
Posts: 6,499

Re: Add column to existing dataset

It is much easier for those reading (and for the you to post) if you just paste your data as TEXT instead of going to the trouble of creating and attaching XLSX files.  But at least you attached files that the website can read, so that I didn't need to download them.

You first file just has this data.

VOLS VOlB
12 58 
45 59 
56 22 
78 30

And your second file has this data.

PROBS 
0.5 
0.8 
0.25 
0.35

If you MERGE the files without a BY statement then SAS will match the records. Then if one of the files is "shorter" the values from the last observation will be retained.  

data want;
  merge test1 test2 ;
  * NO by statement ;
run;

If you wanted the variables contributed by the shorter dataset to be missing when you read past the end then you can add these lines before the RUN statement.

output;
call missing(of _all_);
Ask a Question
Discussion stats
  • 4 replies
  • 193 views
  • 1 like
  • 3 in conversation