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.
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 ;
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.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.