data merging by time for each ID -> so i will have multiple new output datasets

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 15
Accepted Solution

data merging by time for each ID -> so i will have multiple new output datasets

I have two datasets that I need to merge by time.

 

Example-

 

dataset A

time id location

10am 3 boston

10:30am 3 boston

11am 4 newyork

 

dataset B

time weight

10am 150

10:30am 140

11am 170

 

 

I want to merge these two by time for each subject (id). 

Basically after merging, I want to have 2 output datasets, 1 for id=3 and 1 for id=4.

id=3 will have total of 2 observations, and id=4 will have 1 observation.

 

My datasets are actually much larger than these, so I can't do merge if id=3 etc.

I will have to make this a automated process, where SAS will just do "do-loop" of this merging process for each ID...

 

Thanks so much in advance!!!!!!!!!!!!!!1

 


Accepted Solutions
Solution
‎05-18-2016 12:24 PM
Super User
Posts: 9,854

Re: data merging by time for each ID -> so i will have multiple new output datasets

Hash Table:

 

data A;
input (time id location) ($);
cards;
10am 3 boston
10:30am 3 boston
11am 4 newyork
;
run;
 
data B;
input (time weight) ($);
cards;
10am 150
10:30am 140
11am 170
;
run;
data _null_;
 if _n_=1 then do;
  if 0 then set B;
  declare hash h(dataset:'B');
  h.definekey('time');
  h.definedata('weight');
  h.definedone();
  
  if 0 then set A;
  declare hash hh(multidata:'y');
  hh.definekey('id');
  hh.definedata('time','id','location','weight');
  hh.definedone();
 end;

set A;
by id;
call missing(of weight);
rc=h.find();
hh.add();
if last.id then do;
 hh.output(dataset:cats('want',id));
 hh.clear();
end;
run;

View solution in original post


All Replies
Super User
Posts: 18,997

Re: data merging by time for each ID -> so i will have multiple new output datasets

In general creating multiple datasets is not recommended. Can you explain why you'd like to do this?

Solution
‎05-18-2016 12:24 PM
Super User
Posts: 9,854

Re: data merging by time for each ID -> so i will have multiple new output datasets

Hash Table:

 

data A;
input (time id location) ($);
cards;
10am 3 boston
10:30am 3 boston
11am 4 newyork
;
run;
 
data B;
input (time weight) ($);
cards;
10am 150
10:30am 140
11am 170
;
run;
data _null_;
 if _n_=1 then do;
  if 0 then set B;
  declare hash h(dataset:'B');
  h.definekey('time');
  h.definedata('weight');
  h.definedone();
  
  if 0 then set A;
  declare hash hh(multidata:'y');
  hh.definekey('id');
  hh.definedata('time','id','location','weight');
  hh.definedone();
 end;

set A;
by id;
call missing(of weight);
rc=h.find();
hh.add();
if last.id then do;
 hh.output(dataset:cats('want',id));
 hh.clear();
end;
run;
☑ This topic is solved.

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

Discussion stats
  • 2 replies
  • 331 views
  • 0 likes
  • 3 in conversation