topic Re: Combine observations with same IDs in New SAS User

Combine observations with same IDs

kippy_kips — Tue, 02 Aug 2022 19:28:11 GMT

Hi all,

I'm analyzing NHANES data and have combined day 1 food intake and day 2 food intake into their own observation rows. Now I want to combine those rows into 1 row that shows days 1 and 2 intakes for each participant. For example:

SEQ	DR1T_G_WHOLE	DR1T_G_REFINED	DR2T_G_WHOLE	DR2T_G_REFINED
1	5.2	4.01
1			.8	1.2
2	1.0	2.2
2			2.5	0

How would I combine the 1s so all the cells are filled? I also have thousands of SEQ IDs.

I've tried proc means data= test noprint nway;

class SEQ;

var ...;

output out= sums;

run;

This led to a min max and std showing up as well, is there a way to remove those?

Re: Combine observations with same IDs

Reeza — Tue, 02 Aug 2022 19:38:15 GMT

data want;
update test(obs=0) test;
by seq;
run;

Or:

proc means data= test noprint nway;
class SEQ;
var _numeric_;
output out=summarized sum= ;
run;

I think either of those approaches should work for you. But for NHANES data are there weights you need to factor in?

Re: Combine observations with same IDs

Tom — Tue, 02 Aug 2022 19:41:11 GMT

That structure looks it would work well as a transaction file for the UPDATE statement.

data want;
  update have(obs=0) have;
  by seq;
run;

For UPDATE to work the ORIGINAL dataset has to have only one observation per BY group (actually a MAX of one per BY group), but the TRANSACTION dataset can have multiple observations and the transactions will be applied in order. So the result will have the last non-missing value for each variable.

Re: Combine observations with same IDs

kippy_kips — Tue, 02 Aug 2022 20:38:42 GMT

Thank you! The first code you sent kept deleting all observations for some reason but the second one worked! I am just interested in the food groups eaten by participants for right now.