topic Re: Merging multiple numeric column in SAS Programming

Merging multiple numeric column

jhabikaskumar26 — Wed, 30 Jun 2021 08:39:15 GMT

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.

Re: Merging multiple numeric column

andreas_lds — Wed, 30 Jun 2021 08:52:08 GMT

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

Re: Merging multiple numeric column

jhabikaskumar26 — Wed, 30 Jun 2021 09:31:04 GMT

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

Re: Merging multiple numeric column

andreas_lds — Wed, 30 Jun 2021 10:31:23 GMT

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.

Re: Merging multiple numeric column

tarheel13 — Wed, 30 Jun 2021 11:26:52 GMT

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.

Re: Merging multiple numeric column

jhabikaskumar26 — Wed, 30 Jun 2021 11:42:17 GMT

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.

Re: Merging multiple numeric column

Patrick — Wed, 30 Jun 2021 11:57:02 GMT

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

Re: Merging multiple numeric column

Tom — Wed, 30 Jun 2021 12:21:07 GMT

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

Re: Merging multiple numeric column

Tom — Wed, 30 Jun 2021 12:25:31 GMT

@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.

Re: Merging multiple numeric column

Reeza — Wed, 30 Jun 2021 14:28:16 GMT

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.