topic Re: How to Copy data of one variable to another with missing data in it. in SAS Programming

How to Copy data of one variable to another with missing data in it.

Rakesh93 — Tue, 15 Oct 2019 15:54:22 GMT

How to copy data of one variable to another in SAS.
I was using this program
Data DM6;
Set DM5;
RFS TFTC=RFXSTDTC;
Run;

Re: How to Copy data of one variable to another with missing data in it.

PaigeMiller — Fri, 29 Jul 2022 19:47:02 GMT

@Rakesh93 wrote:

RFS TFTC=RFXSTDTC;

You have an unnecessary (and wrong) space in the code. You want RFSTFTC without a space:

data DM6;
   set DM5;
   RFSTFTC=RFXSTDTC;
run;

Editor's Note:

Special thanks to @ChrisNZ and @hashman for sharing how to conditionally assign a value if the original variable is missing using the COALESCE function for numeric values, and the COALESCEC function for character values:

data _NULL_;
   Num_1=.;
   Var_1='    ';
   Var_2='    ';
	NUM_A=10;
	VAR_A='Test';
	put 'NOTE: Before ***********************';
	put 'NOTE: ' NUM_1= NUM_2= NUM_A=;
	put 'NOTE- ' VAR_1= VAR_2= VAR_A=;
	NUM_1=coalesce(NUM_1, NUM_A);
	VAR_1=coalescec(VAR_1, VAR_A);
	put 'NOTE: After first *******************';
	put 'NOTE: ' NUM_1= NUM_2= NUM_A=;
	put 'NOTE- ' VAR_1= VAR_2= VAR_A=;
	NUM_2=coalesce(NUM_1, NUM_A);
	VAR_2=coalescec(VAR_1, VAR_A);
	put 'NOTE: After last *******************';
	put 'NOTE: ' NUM_1= NUM_2= NUM_A=;
	put 'NOTE- ' VAR_1= VAR_2= VAR_A=;
run;

And the result:

NOTE: Before ***********************
NOTE: Num_1=. NUM_2=. NUM_A=10
      Var_1=  Var_2=  VAR_A=Test

NOTE: After first *******************
NOTE: Num_1=10 NUM_2=. NUM_A=10
      Var_1=Test Var_2=  VAR_A=Test

NOTE: After last *******************
NOTE: Num_1=10 NUM_2=10 NUM_A=10
      Var_1=Test Var_2=Test VAR_A=Test

Re: How to Copy data of one variable to another with missing data in it.

ravig — Tue, 15 Oct 2019 16:17:41 GMT

Lets say there are two variables VAR1 and VAR2. VAR2 have some missing values and you want to fill the missing values with values from VAR1.

Data NEW;

set Sorucedataset;

If Var2=" " then VAR2=VAR1;else VAR2=VAR2;

run;

Re: How to Copy data of one variable to another with missing data in it.

ChrisNZ — Wed, 16 Oct 2019 02:26:03 GMT

Like this?

RFSTFTC=coalesce(RFSTFTC,RFXSTDTC);

for a numeric variable.

Re: How to Copy data of one variable to another with missing data in it.

hashman — Wed, 16 Oct 2019 00:34:56 GMT

@ravig: Heed what @ChrisNZ has said.

If your variables are character, use the COALESCEC function instead.

If you want to make you code data type insensitive, use SQL where the single COALESCE function is good for both numeric or character variables, e.g.:

proc sql ;
  create table want as select coalesce (var1, var2) as var2 from have ;
quit ;

Kind regards

Paul D.