BookmarkSubscribeRSS Feed
dhir
Obsidian | Level 7

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

4 REPLIES 4
Astounding
PROC Star

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.

dhir
Obsidian | Level 7

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

 

Astounding
PROC Star

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

Tom
Super User Tom
Super User

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_);

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
  • 4 replies
  • 7293 views
  • 1 like
  • 3 in conversation