BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
rakeshvvv
Quartz | Level 8

Hi,

Can some one help me the the following query. I have data set with 2 Variables ID and VISIT..

100010001SAFETY FOLLOW UP
100010001SCREENING
100010001EOT
100010001C1D1
100010001C1D2
100010001C1D3
100010001C1D4
100010001C1D5

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 ','

100010001    SAFETY FOLLOW UP,SCREENING,EOT,C1D1,C1D2,C1D3,C1D4,C1D5

THANKS IN ADVANCE

RAKESH

1 ACCEPTED SOLUTION

Accepted Solutions
Tom
Super User Tom
Super User

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;

View solution in original post

5 REPLIES 5
Reeza
Super User

Tom
Super User Tom
Super User

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;

Jagadishkatam
Amethyst | Level 16

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

Thanks,
Jag
Astounding
PROC Star

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.

Ksharp
Super User

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 5 replies
  • 937 views
  • 5 likes
  • 6 in conversation