SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

How to track changing values for same ID across 5 datasets

Accepted Solution Solved
Reply
Contributor
Posts: 20
Accepted Solution

How to track changing values for same ID across 5 datasets

Hi SAS community,

 

 I have 4 datasets that have data on same population across 6 time points.

 

I want to know how to perform the occurrence as show below.

 

Dataset 1

 

ID       Var 1

1           0

2           0

3           0

4           0

5           0

 

Dataset 2

 

ID       Var 1

1           0

2           0

3           1

4           0

5            0

 

Dataset 3

 

ID       Var 1

1           1

2           0

3           1

4           0

5           0

 

Dataset 4

 

ID       Var 1

1           1

2           0

3           0

4           1

5           0

 

I want to find of all the people who had an occurrence of 1 at-least once across the 4 time points or Datasets. 


Accepted Solutions
Solution
3 weeks ago
Contributor
Posts: 28

Re: How to track changing values for same ID across 5 datasets

I would merge all of the datasets, then use SQL.

 

proc sql;
  create table mergedSet as select * from Dataset1;
  insert into mergedSet values(select * from Dataset2);
  insert into mergedSet values(select * from Dataset3);
  insert into mergedSet values(select * from Dataset4);
  insert into mergedSet values(select * from Dataset5);

  select id, sum(var1)>0 as hasValue from mergedSet 
  group by id;
quit;

View solution in original post


All Replies
Solution
3 weeks ago
Contributor
Posts: 28

Re: How to track changing values for same ID across 5 datasets

I would merge all of the datasets, then use SQL.

 

proc sql;
  create table mergedSet as select * from Dataset1;
  insert into mergedSet values(select * from Dataset2);
  insert into mergedSet values(select * from Dataset3);
  insert into mergedSet values(select * from Dataset4);
  insert into mergedSet values(select * from Dataset5);

  select id, sum(var1)>0 as hasValue from mergedSet 
  group by id;
quit;
Super User
Posts: 5,616

Re: How to track changing values for same ID across 5 datasets

A data step approach:

 

data want;

set dataset1 dataset2 dataset3 dataset4;

by ID;

where var1=1;

if first.ID;

keep ID;

run;

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 2 replies
  • 145 views
  • 1 like
  • 3 in conversation