topic Re: concatenate the three data sets by its observation in SAS Programming

concatenate the three data sets by its observation

Ashraya — Tue, 16 Jun 2015 02:13:43 GMT

I want to concatenate the three data sets by its observation, please give me the idea to proceed on it.

data set A;

Num Var1

1 abc

4 xyz

5 mno

Data set B;

Num Var1

2 aaa

5 qqq

7 zzz

Data set C;

Num Var1

3 qwe

6 ert

4 rty

Result dataset should be as below:

Num Var1

1 abc

2 aaa

3 qwe

4 xyz

5 qqq

6 ert

5 mno

7 zzz

4 rty

The result dataset should be read first combine observation of each dataset, then second observations , so on so forth. Its like dataset A have questions data. B has answers data and C has explanation data. The result should be Question, answer and explanation in the order.

thanks and regards

Ash

Re: concatenate the three data sets by its observation

Tom — Tue, 16 Jun 2015 12:54:48 GMT

Sort them first and use SET with a BY statement.

data want;

set a b c ;

by num;

run;

Or set them first without the BY and then use PROC SORT to order the results.

Re: concatenate the three data sets by its observation

Steelers_In_DC — Tue, 16 Jun 2015 13:27:45 GMT

You'll have to add some logical way to order these. This will work:

data seta ;

input num var1 $;

cards;

1 abc

4 xyz

5 mno

;

data setb ;

input num var1 $;

cards;

2 aaa

5 qqq

7 zzz

;

data setc ;

input num var1 $;

cards;

3 qwe

6 ert

4 rty

;

data seta;

set seta;

count +1;

run;

data setb;

set setb;

count +1;

run;

data setc;

set setc;

count +1;

run;

data want(drop=count);

set seta setb setc;

by count;

run;

Re: concatenate the three data sets by its observation

ballardw — Tue, 16 Jun 2015 15:57:21 GMT

A somewhat different approach:

data seta ;
input num var1 $;
order=_n_*3;
cards;
1 abc
4 xyz
5 mno
;
run;

data setb ;
input num var1 $;
order=_n_*3 +1;
cards;
2 aaa
5 qqq
7 zzz
;
run;

data setc ;
input num var1 $;
order=_n_*3+2;
cards;
3 qwe
6 ert
4 rty
;
run;

data want;
merge seta setb setc;
by order;

drop order;

run;

Re: concatenate the three data sets by its observation

Tom — Tue, 16 Jun 2015 16:03:31 GMT

If the datasets all have the same number of observations then you could do this (without any sorting).

data want;

set a; output;

set b; output;

set c; output;

run;

Now if one or more of the datasets has fewer observations than the others this will stop as soon as it reads past the end of the shortest dataset.

Re: concatenate the three data sets by its observation

Astounding — Tue, 16 Jun 2015 17:25:38 GMT

Even when the data sets have different numbers of observations, you can program around it:

data want;

if end1=0 then do;

set a end=end1; output;

end;

if end2=0 then do;

set b end=end2; output;

end;

if end3=0 then do;

set c end=end3; output;

end;

if end1 and end2 and end3 then stop;

run;

Re: concatenate the three data sets by its observation

Haikuo — Tue, 16 Jun 2015 18:03:28 GMT

Or 3XDOW:

data want;

do _n_=1 to 1 while (not end1);

set a end=end1;

output;

end;

do _n_=1 to 1 while (not end2);

set b end=end2;

output;

end;

do _n_=1 to 1 while (not end3);

set c end=end3;

output;

end;

run;

Re: concatenate the three data sets by its observation

Tom — Tue, 16 Jun 2015 18:18:44 GMT

That won't do what the OP wanted. Instead it does the same thing as :

data want; set a b c; run;

Re: concatenate the three data sets by its observation

Astounding — Tue, 16 Jun 2015 18:27:15 GMT

I think it won't quite work, but for a different reason. I think you will get one extra observation added to the data set at the end, with a message about the data step ending due to looping. (Too busy to test it right now, but that's what I would expect.)

Re: concatenate the three data sets by its observation

Haikuo — Tue, 16 Jun 2015 18:27:30 GMT

Notice '_n_=1 to 1', This time I stand correctly, Tom

Re: concatenate the three data sets by its observation

Haikuo — Tue, 16 Jun 2015 18:32:35 GMT

You are right about 'due to looping', not sure how that affects the final results, maybe just performance?

data class1 class2;

set sashelp.class;

if _n_<10 then

output class1;

output class2;

run;

data want;

do _n_=1 to 1 while (not end1);

set class2 end=end1;

output;

end;

do _n_=1 to 1 while (not end2);

set class1 end=end2;

output;

end;

run;

NOTE: DATA STEP stopped due to looping.

NOTE: There were 19 observations read from the data set WORK.CLASS2.

NOTE: There were 9 observations read from the data set WORK.CLASS1.

2 The SAS System 07:45 Wednesday, June 10, 2015

NOTE: The data set WORK.WANT has 28 observations and 5 variables.

NOTE: Compressing data set WORK.WANT increased size by 100.00 percent.

Compressed is 2 pages; un-compressed would require 1 pages.

NOTE: DATA statement used (Total process time):

real time 0.01 seconds

cpu time 0.02 seconds

Edit:

Should have added one of your statements at the end: if end1 and end2 and end3 then stop;

Re: concatenate the three data sets by its observation

Astounding — Tue, 16 Jun 2015 18:41:17 GMT

You're right, it's just performance (plus an annoying message). Because of the presence of OUTPUT statements, the program doesn't create that extra record at the end. Contrast that with:

data want;

if not eof then set have end=eof;

run;

Re: concatenate the three data sets by its observation

Haikuo — Tue, 16 Jun 2015 18:44:32 GMT

Thanks, Robert.

Re: concatenate the three data sets by its observation

Tom — Tue, 16 Jun 2015 19:49:58 GMT

Normally I would code DO _N_ = 1 BY 1 UNTIL(...), so I just assumed you had done the same.

But in that case the _N_=1 to 1 is doing nothing of value. Why not just IF not EOF1 then DO; ?

Re: concatenate the three data sets by its observation

Haikuo — Tue, 16 Jun 2015 19:54:24 GMT

Absolutely, Tom. Mine resulted from the old habit format that DOW usually takes. The sole purpose, as you noted, is to force 'set' only once at a time, otherwise, it would be identical to what Astounding does, which btw is much clearer regarding the programming logic.

Re: concatenate the three data sets by its observation

Ashraya — Wed, 17 Jun 2015 02:44:44 GMT

Thank you all for the replay. it worked.