topic How to collect obs from several dataset into individual datasets? in SAS Programming

How to collect obs from several dataset into individual datasets?

yanshuai — Wed, 21 Mar 2018 00:20:48 GMT

Hello,

I have dataset several like this.

data have2000 ;
input
ID$4. SALE YEAR;
datalines;
6734 28 2000
1234 23 2000
0034 32 2000
2234 44 2000
;
run;

data have2001 ;
input
ID$4. SALE YEAR;
datalines;
6734 17 2001
1234 52 2001
0034 75 2001
2234 44 2001
1234 26 2001
;
run;

data have2002 ;
input
ID$4. SALE YEAR;
datalines;
6734 11 2002
1234 23 2002
0034 33 2002
2234 15 2002
1234 13 2002
8334 44 2002
;
run;

data have2003 ;
input
ID$4. SALE YEAR;
datalines;
6734 56 2003
1234 43 2003
0034 35 2003
2234 44 2003
1234 45 2003
8334 23 2003
2234 22 2003
;
run;

This is what I would like to acheive.

Collect all the observation with the same ID over all dataset I have, and put those observation together in a new dataset and name the new dataset by its ID. Their variable name is basically the same.

data want_id_6734;
input
ID$4. SALE YEAR;
datalines;
6734 56 2003
6734 11 2002
6734 17 2001
6734 28 2000
;
run;

data want_id_1234;
input
ID$4. SALE YEAR;
datalines;
1234 43 2003
1234 45 2003
1234 23 2002
1234 13 2002
1234 52 2001
1234 26 2001
1234 23 2000
;
run;

data want_id_0034;
input
ID$4. SALE YEAR;
datalines;
0034 35 2003
0034 33 2002
0034 75 2001
0034 32 2000
;
run;
data want_id_2234;
input
ID$4. SALE YEAR;
datalines;
2234 44 2003
2234 22 2003
2234 15 2002
2234 44 2001
2234 44 2000
;
run;

data want_id_8334;
input
ID$4. SALE YEAR;
datalines;
8334 23 2003
8334 44 2002
;
run;

How can I achieve this? I simply find it a little cumberrsome using SQL and WHERE. Since my actual dataset is from 2000 to 2008, there will be a lot of WHERE condition if use SQL.

I am thinking acheive this using MACRO. How can I do this?

Thank you all very much! Appreciate it.

Re: How to collect obs from several dataset into individual datasets?

novinosrin — Wed, 21 Mar 2018 00:18:00 GMT

data have2000 ;
input
ID$4. SALE YEAR;
datalines;
6734 28 2000
1234 23 2000
0034 32 2000
2234 44 2000
;
run;

data have2001 ;
input
ID$4. SALE YEAR;
datalines;
6734 17 2001
1234 52 2001
0034 75 2001
2234 44 2001
1234 26 2001
;
run;

data have2002 ;
input
ID$4. SALE YEAR;
datalines;
6734 11 2002
1234 23 2002
0034 33 2002
2234 15 2002
1234 13 2002
8334 44 2002
;
run;

data have2003 ;
input
ID$4. SALE YEAR;
datalines;
6734 56 2003
1234 43 2003
0034 35 2003
2234 44 2003
1234 45 2003
8334 23 2003
2234 22 2003
;
run;

data temp;
set have:;
run;



data _null_ ; 
  dcl hash x() ; 
  x.defineKey ('id') ; 
  x.defineData ('id','h') ; 
  x.defineDone () ; 
  dcl hash h ; 
  do until (z) ; 
    set temp end = z ; 
    if x.find() ne 0 then do ; 
      h = _new_ hash (dataset:'temp(obs=0)', multidata:'y') ;
      h.defineKey ('id') ; 
      h.defineData (all:'y') ; 
      h.defineDone () ; 
      x.add() ; 
    end ; 
    h.add() ; 
  end ; 
  dcl hiter i('x') ; 
  do while (i.next()=0) ; 
    h.output (dataset: cats('id_',id)) ; 
  end ; 
  stop ; 
run ;

Re: How to collect obs from several dataset into individual datasets?

Reeza — Wed, 21 Mar 2018 00:47:19 GMT

Slight suggested modification to @novinosrin code, create a view instead.

data temp / view=temp;
set have:;
run;

@yanshuai is there a particular reason you're doing this? It's usually never recommended and BY group processing is quite powerful in SAS.