BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
joebacon
Pyrite | Level 9

Hi, 

I want to subtract two different variables that are populated on different observations within the same ID. A simplified version of my data is presented below:

 

data work.test ;
  infile datalines dsd dlm='|' truncover;                                                       

  input redcap_event_name :$23. motivate_weightloss
    postvisit_motivate_wgtloss ID
  ;
  format redcap_event_name $23. motivate_weightloss best.
    postvisit_motivate_wgtloss best.
  ;
  informat redcap_event_name $23. ;
  label redcap_event_name='redcap_event_name'
    motivate_weightloss='motivate_weightloss'
    postvisit_motivate_wgtloss='postvisit_motivate_wgtloss'
  ;
datalines4;
screener_arm_1|10||1
baseline_arm_1|||1
post_appointment_arm_1||8|1
12_week_follow_up_arm_1|||1
screener_arm_1|8||2
baseline_arm_1|||2
post_appointment_arm_1||7|2
12_week_follow_up_arm_1|||2
screener_arm_1|9||4
baseline_arm_1|||4
post_appointment_arm_1||9|4
12_week_follow_up_arm_1|||4
screener_arm_1|8||6
baseline_arm_1|||6
post_appointment_arm_1||8|6
12_week_follow_up_arm_1|||6
screener_arm_1|9||8
baseline_arm_1|||8
post_appointment_arm_1||7|8
12_week_follow_up_arm_1|||8
;;;;

I want  postvisit_motivate_wgtloss - motivate_weightloss but postvisit_motivate_wgtloss is only populated at the post_appointment_arm and motivate_weightloss is only populated at the screener_arm. This seems like it should be really simple. 

 

I tried the following which didnt quite get me there but I think it is close. I am trying to streamline my code for efficiency because I need to do this several times. I think it is worth mentioning that it is also a big dataset.

 

DATA	WORK.Test_summary;
	SET	work.test;
		BY	ID;

		DROP	S_:	redcap_event_name;

	RETAIN	S_BL_motivate_weightloss S_BL_postvisit_motivate_wgtloss S_FU_motivate_weightloss S_FU_postvisit_motivate_wgtloss;

	IF	NOT	MISSING(BL_motivate_weightloss)				THEN
		S_BL_motivate_weightloss	=	BL_motivate_weightloss;

	IF	NOT	MISSING(FU_postvisit_motivate_wgtloss)					THEN
		S_FU_postvisit_motivate_wgtloss		=	FU_postvisit_motivate_wgtloss;

	IF	LAST.ID										THEN
		DO;
			BL_motivate_weightloss	=	S_BL_motivate_weightloss;
			FU_postvisit_motivate_wgtloss		=	S_FU_postvisit_motivate_wgtloss;
			
			IF	NOT	MISSING(BL_motivate_weightloss)		THEN
				BL_Wt			=	BL_motivate_weightloss;
			IF	NOT	MISSING(FU_postvisit_motivate_wgtloss)			THEN
				FU_Wt			=	FU_postvisit_motivate_wgtloss;

			Wt_change			=	FU_Wt - BL_wt;
			OUTPUT;
			CALL	MISSING(S_BL_motivate_weightloss, S_BL_postvisit_motivate_wgtloss, S_FU_motivate_weightloss, S_FU_postvisit_motivate_wgtloss);
		END;
RUN;
PROC	SQL;
	CREATE TABLE	WORK.Test_Final
		AS	SELECT	Detail.*
			,Summary.BL_Wt
			,Summary.FU_Wt
			,Summary.Wt_Change
			FROM	WORK.Test					Detail
			INNER	JOIN	WORK.Test_Summary	Summary
				ON	Detail.ID	=	Summary.ID
				;
QUIT;

Any help would be appreciated.

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
PGStats
Opal | Level 21

You only need one proc sql step:

 

proc sql;
/* create table test_final as */
select
    a.ID,
    a.motivate_weightloss,
    b.postvisit_motivate_wgtloss,
    b.postvisit_motivate_wgtloss - a.motivate_weightloss as weightloss_change
from 
    test as a left join
    test as b on a.id=b.id
where 
    a.redcap_event_name = "screener_arm_1" and 
    b.redcap_event_name = "post_appointment_arm_1";
quit;

image.png

PG

View solution in original post

3 REPLIES 3
PGStats
Opal | Level 21

You only need one proc sql step:

 

proc sql;
/* create table test_final as */
select
    a.ID,
    a.motivate_weightloss,
    b.postvisit_motivate_wgtloss,
    b.postvisit_motivate_wgtloss - a.motivate_weightloss as weightloss_change
from 
    test as a left join
    test as b on a.id=b.id
where 
    a.redcap_event_name = "screener_arm_1" and 
    b.redcap_event_name = "post_appointment_arm_1";
quit;

image.png

PG
joebacon
Pyrite | Level 9
You're a wizard. Thank you so much!
jimbarbour
Meteorite | Level 14

@joebacon,

 

I think the following code should do what you're asking, yes?

DATA	WORK.Consolidated_Test(RENAME=(	Sv_Motivate_Weightloss			=	Motivate_Weightloss
										Sv_Postvisit_Motivate_Wgtloss	=	Postvisit_Motivate_Wgtloss));
	DROP	Redcap_Event_Name;
	FORMAT	ID Sv_Motivate_Weightloss	Sv_Postvisit_Motivate_Wgtloss	Weight_Change;
	RETAIN	Sv_Motivate_Weightloss		Sv_Postvisit_Motivate_Wgtloss;

	SET	WORK.TEST;
		BY	ID	
			NOTSORTED
			;

	IF	NOT	MISSING(Motivate_Weightloss)		THEN
		Sv_Motivate_Weightloss			=	Motivate_Weightloss;

	IF	NOT	MISSING(Postvisit_Motivate_Wgtloss)	THEN
		Sv_Postvisit_Motivate_Wgtloss	=	Postvisit_Motivate_Wgtloss;

	IF	LAST.ID									THEN
		DO;
			Weight_Change				=	Sv_Motivate_Weightloss	-	Sv_Postvisit_Motivate_Wgtloss;
			OUTPUT;
		END;
	ELSE
		DO;
			DELETE;
		END;
RUN;

If you know for a fact that the ID's are going to be in sort order than you can use the NOTSORTED option that I've specified above.  Otherwise you would need to add a Proc Sort and removed the NOTSORTED.

 

Results are one line per ID as shown below.

jimbarbour_0-1601343381915.png

 

Jim

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 3 replies
  • 494 views
  • 2 likes
  • 3 in conversation