topic Re: How to merge and create new records ? in SAS Programming

How to merge and create new records ?

chetan3125 — Wed, 22 Jun 2022 15:38:02 GMT

I am trying to merge and create new records.

However with following code, the records for new2 are not output.

I want records for new1 and new2 in the final data based on the conditions.

Any solutions ? or may be sql alternative ?
Thanks.

data d1 ;
input VISIT  PARAMCD $3-10 PATHOGEN $11-13 subject $14-16;
datalines ;
10 param3 P1 1
40 param1 P2 1
40 param3 P2 1
40 param2 P2 1
10 param1 P2 2
10 param3 P2 2
10 param2 P2 2
40 param1 P3 3
40 param3 P3 3
40 param2 P3 3
;
run;

Data param1 param2 param3 ;
set d1 ;
		 if paramcd='param1' then output  param1 ;
		 if paramcd='param2' then output param2 ;
		 if paramcd='param3' then output param3 ;
drop paramcd ;
run ;

data d2 ;
	merge d1 (in=a where= (visit ne . )) 
		param1  (in=param1  ) 
		param2 (in=param2 )
		param3 (in=param3 )
	;by subject visit pathogen ;
		if paramcd='param1' and param2 then do ; paramcd= 'new1' ; output ;end; 
		if paramcd='param1' and param3 then do ; paramcd= 'new2' ; output ;end; 
Run;

Re: How to merge and create new records ?

CarmineVerrell — Wed, 22 Jun 2022 15:46:25 GMT

Seems you have an issue with the logic and you dropped the column paramcd, so i have modified code and included below.

data d1 ;
input VISIT PARAMCD $3-10 PATHOGEN $11-13 subject $14-16;
datalines ;
10 param3 P1 1
40 param1 P2 1
40 param3 P2 1
40 param2 P2 1
10 param1 P2 2
10 param3 P2 2
10 param2 P2 2
40 param1 P3 3
40 param3 P3 3
40 param2 P3 3
;
run;

Data param1 param2 param3 ;
set d1 ;
if paramcd='param1' then output param1 ;
if paramcd='param2' then output param2 ;
if paramcd='param3' then output param3 ;

run ;

data d2 ;
merge d1 (in=a where= (visit ne . ))
param1 (in=param1 )
param2 (in=param2 )
param3 (in=param3 ) ;
by subject visit pathogen ;
if paramcd='param1' and param1 then do ; paramcd= 'new1' ; output ;end;
if paramcd='param2' and param1 then do ; paramcd= 'new2' ; output ;end;
Run;

Re: How to merge and create new records ?

ballardw — Wed, 22 Jun 2022 15:56:56 GMT

Please look at the result from this code:

data d2 ;
	merge d1 (in=a where= (visit ne . )) 
		param1  (in=param1  ) 
		param2 (in=param2 )
		param3 (in=param3 )
	;by subject visit pathogen ;
		if paramcd='param1' and param2 then do ; paramcdnew= 'new1' ; output ;end; 
		if paramcd='param1' and param3 then do ; paramcdnew= 'new2' ; output ;end; 
Run;

At least with your example data, if I understand what you are attempting, when you set Paramcd to 'new1' the value is no longer there for use in the second IF.

Try this: Test the Paramcd value only one time.

data d2 ;
	merge d1 (in=a where= (visit ne . )) 
		param1  (in=param1  ) 
		param2 (in=param2 )
		param3 (in=param3 )
	;by subject visit pathogen ;
	if paramcd='param1' Then do;
           if param2 then do ; paramcd= 'new1' ; output ;end; 
	   if param3 then do ; paramcd= 'new2' ; output ;end; 
       end;
Run;

Re: How to merge and create new records ?

chetan3125 — Wed, 22 Jun 2022 16:29:01 GMT

@ballardw This worked. Thanks. Also changed the output var name.