Combining two variables-data collected under two different variable names actually belongs to one

[kk11]

Hi, 

I have two variables named CCR_NIS and APICC. There are missing observations in these two variables. 

Actually these are not missing data.

The data for year 2015 was collected under the variable named CCR_NIS and the similar data for year 2011 was collected under the variable named APICC.

Actually all the data should be in one variable, as the data under these two variables named CCR_NIS and APICC is same. 

I want to move the data from APICC in to CCR_NIS for corresponding observations where it is empty in CCR_NIS, so that all my data is under CCR_NIS and finally I can delete the variable APICC. 

My final data set will have CCR_NIS only without any  

Please find the data below.

I had hard time in figuring out the code to perform this task.

Please help me in writing the code for this Task.

Thank you very much.

CCR_NIS	APICC	YEAR
0.274		2015
0.274		2015
0.399		2015
0.414		2015
0.298		2015
0.315		2015
0.315		2015
0.299		2015
0.29		2015
0.298		2015
0.298		2015
	0.263067	2011
	0.263067	2011
	0.263067	2011
	0.253981	2011
	0.253981	2011
	0.253981	2011
	0.253981	2011
	0.215371	2011
	0.215371	2011
	0.215371	2011
	0.215371	2011
	0.215371	2011
	0.215371	2011

[VDD]

as long as the data variables are the same type why not use rename 

Proc freq data=sashelp.class( rename=(sex=gender));
tables gender;
run;

( rename=(APICC = CCR_NIS))

[kk11]

I got the following error.

1 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;

61

62 Proc freq data=Sample.Sample_CLEAN2( rename=(APICC = CCR_NIS));

ERROR: Variable CCR_NIS already exists on file Sample.Sample_CLEAN2.

ERROR: Invalid DROP, KEEP, or RENAME option on file Sample.Sample_CLEAN2.

63 tables CCR_NIS;

ERROR: No data set open to look up variables.

64 run;

NOTE: The SAS System stopped processing this step because of errors.

NOTE: PROCEDURE FREQ used (Total process time):

real time 0.00 seconds

cpu time 0.00 seconds

65

66 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;

79

[kk11]

I just don't want to rename the APICC.

I want to move the data in APICC variable to empty observations in CCR_NIS variable.

So that there wont be any data in APICC and all the data will be in CCR_NIS.

Finally I can delete the APICC variable from the dataset.

[VDD]

data have;
input CCR_NIS :8. APICC :8.  YEAR :$4.;
datalines;
0.274 . 2015
0.274 . 2015
0.399 . 2015
0.414 . 2015
0.298 . 2015
0.315 . 2015
0.315 . 2015
0.299 . 2015
0.29 . 2015
0.298 . 2015
0.298 . 2015
. 0.263067 2011
. 0.263067 2011
. 0.263067 2011
. 0.253981 2011
. 0.253981 2011
. 0.253981 2011
. 0.253981 2011
. 0.215371 2011
. 0.215371 2011
. 0.215371 2011
. 0.215371 2011
. 0.215371 2011
. 0.215371 2011
;
data want;
	set have;
	if CCR_NIS = . then CCR_NIS = APICC;
	drop APICC;
run;

[kk11]

your code worked too.

I checked it after COALESCE function.

Thank you very much.

[FreelanceReinh · Accepted solution]

Hi @kk11,

Why don't you use the COALESCE function?

data have;
input CCR_NIS APICC year;
cards;
0.298 .        2015
.     0.263067 2011
;

data want(drop=apicc);
set have;
ccr_nis=coalesce(ccr_nis, apicc);
run;

[kk11]

The COALESCE function worked.

I was able to combine two variables in to one.

Thank you very much.