Help using Base SAS procedures

SAS QUERY

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 145
Accepted Solution

SAS QUERY

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


Accepted Solutions
Solution
‎06-01-2015 12:57 PM
Super User
Super User
Posts: 7,078

Re: SAS QUERY

Posted in reply to rakeshvvv

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


All Replies
Super User
Posts: 19,873

Re: SAS QUERY

Posted in reply to rakeshvvv

Solution
‎06-01-2015 12:57 PM
Super User
Super User
Posts: 7,078

Re: SAS QUERY

Posted in reply to rakeshvvv

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;

Trusted Advisor
Posts: 1,137

Re: SAS QUERY

Posted in reply to rakeshvvv

Please try

proc transpose data=have out=trans;

by id;

var visit;

run;

data want;

length x $ 100;

set trans;

x=catx(',', of colSmiley Happy;

keep id x;

run;

Thanks,
Jag

Thanks,
Jag
Super User
Posts: 5,518

Re: SAS QUERY

Posted in reply to rakeshvvv

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.

Super User
Posts: 10,046

Re: SAS QUERY

Posted in reply to Astounding

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

🔒 This topic is solved and locked.

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

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