Merging multiple numeric column

[jhabikaskumar26]

I have multiple numeric columns name SAS_PREDICT_1_1,SAS_PREDICT_2_1,SAS_PREDICT_3_1 and so on in a loop consists of numeric value or missing value.As shown in image below.At a time only one column contain value rest all is missing. So, I want to combine all columns in one. Please do help me with this.

[andreas_lds]

Please post data in usable form and show what you expect as result.

[jhabikaskumar26]

I have inserted a sample data in tabular form. Here we can observe that at a time one column of sas_predict consists of a value and rest are empty. So I want to combine them all in column. here it is not necesary that their are only 3 sas_predict column. It can vary.

Sample Dataset

sas_predict_1_2	month_indicator	sas_predict_2_2	sas_predict_3_2
1300.338	22021
10380.55	22021
2670.189	22021
0.131216	22021
8994.803	22021
6053.093	22021
	32021	1307.851976
	32021	10019.19253
	32021	2587.392247
	32021	0.107995115
	32021	8815.3237
	32021	5998.175106
	42021		17487.16651
	42021		5537.772474
	42021		3.28347965
	42021		1006.858698
	42021		6438.711012
	42021		1747.337959

Required Result

sas_predict_1
1300.338
10380.55
2670.189
0.131216
8994.803
6053.093
1307.852
10019.19
2587.392
0.107995
8815.324
5998.175
17487.17
5537.772
3.28348
1006.859
6438.711
1747.338

[andreas_lds]

The data is still not in usable form. Something like

sas_predict_1 = coalesce(sas_predict_1_2, sas_predict_2_2 ...);

should give you the expected values.

[jhabikaskumar26]

Yes this works, But is their any way to automate it. Like if specify any variable say,
n=3 then it should works as sas_predict_1=coalesce(sas_predict_1_2, sas_predict_2_2,sas_predict_3_2);
and n=4 then sas_predict_1=coalesce(sas_predict_1_2, sas_predict_2_2,sas_predict_3_2,sas_predict_4_2);
and so on.

[Tom]

---

@jhabikaskumar26 wrote:
Yes this works, But is their any way to automate it. Like if specify any variable say,
n=3 then it should works as sas_predict_1=coalesce(sas_predict_1_2, sas_predict_2_2,sas_predict_3_2);
and n=4 then sas_predict_1=coalesce(sas_predict_1_2, sas_predict_2_2,sas_predict_3_2,sas_predict_4_2);
and so on.

---

Only if you name your variables in a workable way.  If they sequence that changes is at the end you can use a normal variable list.

coalesce( of sas_predict_1_2 - sas_predict_2_4)

But having the sequence number that changes buried in the middle of the name makes that impossible.

You could try using a prefix variable list:

coalesce( of sas_predict_: )

But that would select any variable whose name starts with that string.  So sas_predict_1_4 and sas_predict_4_2 etc.

[tarheel13]

Usually on here, they want you to post the data as datalines. Anyway, you could also split your dataset into 3 separate datasets and concatenate them back together. 

[Patrick]

If your predict variables follow some naming pattern then something like below should work.

data have;
  length id 8;
  array predict_ {6} 8;
  do id=1 to 10;
    predict_{mod(id,6)+1}=id;
    output;
    call missing(of predict_[*]);
  end;
run;

proc print data=have;
run;

data want;
  set have;
  array pred {*} predict_:;
  pred_combined=coalesce(of pred[*]);
  drop predict_:;
run;

proc print data=want;
run;

Have

Want

[Tom]

The ARRAY is not needed.  The OF keyword will let you use the variable list directly in the function call.

[Reeza]

Here is a reference that illustrates how to refer to variables and datasets in a short cut list:
https://blogs.sas.com/content/iml/2018/05/29/6-easy-ways-to-specify-a-list-of-variables-in-sas.html

You can use many of the methods above to dynamically create the list of variables within a data set.

x = sum(of x1-x10);
x = sum(of x:);
x = sum(of x1--x10);
x=sum(x1, x2, x3, x4, x5, x6, x7, x8, x9, x10);

---

@jhabikaskumar26 wrote:

I have multiple numeric columns name SAS_PREDICT_1_1,SAS_PREDICT_2_1,SAS_PREDICT_3_1 and so on in a loop consists of numeric value or missing value.As shown in image below.At a time only one column contain value rest all is missing. So, I want to combine all columns in one. Please do help me with this.

---