Hi,
Can some one help me the the following query. I have data set with 2 Variables ID and VISIT..
100010001 | SAFETY FOLLOW UP |
100010001 | SCREENING |
100010001 | EOT |
100010001 | C1D1 |
100010001 | C1D2 |
100010001 | C1D3 |
100010001 | C1D4 |
100010001 | C1D5 |
I should create an output in such a way that there should be only record for ID and new variable 'X should have all the values visit separated by ','
THANKS IN ADVANCE
RAKESH
Here is one way using "DOW" loop. That is by placing the SET statement inside of the DO loop.
data want ;
do until (last.id);
set have ;
by id ;
length X $200 ;
x=catx(',',x,visit);
end;
keep id x ;
run;
Here is one way using "DOW" loop. That is by placing the SET statement inside of the DO loop.
data want ;
do until (last.id);
set have ;
by id ;
length X $200 ;
x=catx(',',x,visit);
end;
keep id x ;
run;
Please try
proc transpose data=have out=trans;
by id;
var visit;
run;
data want;
length x $ 100;
set trans;
x=catx(',', of col:);
keep id x;
run;
Thanks,
Jag
SQL isn't my strong point, but it seems like the most straightforward solution:
proc sql noprint;
create table want as select id, strip(visit) separated by ', ' as x
group by id
from have;
run;
Might need tweaking for syntax, but that's the idea anyway.
Astounding,
You mixed up with statement creating a macro variable in SQL . You can not use SEPARATE except you are creating a macro variable.
But I hope one day, SAS SQL could have such power like your code . It is very intuitive .
data have;
infile cards expandtabs truncover;
input a : $20. b & $40.;
cards;
100010001 SAFETY FOLLOW UP
100010001 SCREENING
100010001 EOT
100010001 C1D1
100010001 C1D2
100010001 C1D3
100010001 C1D4
100010001 C1D5
;
run;
data want;
set have;
by a;
length x $ 2000;
retain x;
x=catx(',',x,b);
if last.a then do;output;call missing(x);end;
run;
Xia Keshan
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 the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.