Two rows data into one row

[NareshAbburi]

Hi,

Can someone help me with the below issue,

I've few columns in a tabls like showing below image. I want the second image as my output.

How can I do it using SQL.

The query which I've used to get first table is:

PROC SQL ;

CREATE TABLE MTG.NEW AS

SELECT  DISTINCT CLIENTNAME, VERSION FROM MTG.NEW_CLIENT;

[Patrick]

Using a SAS data step would be much easier. Does it need to be a SQL? And if yes: why?

[NareshAbburi]

Hi,

no need of Sql. But I'm not sure of how to do it using DATA step.

could you please explain me..?

[Reeza]

try something like the following;

data want;

set have;

length new_var $256.;

retain new_var;

by client_id;

if first.client_id then new_var=var;

else new_var=catx(',', new_var, var);

if last.client_id then output;

run;

[stat_sas]

proc sql;

create table MTG.NEW as

select clientname,case when min(version)=max(version) then put(min(version),8.1) else

catx(",",min(version),max(version)) end as version from have

group by clientname;

quit;

[NareshAbburi]

Hi,

Thanks for your reply.

The code is very good for my purpose although it has got some data type issue with PUT function.

[amats · Accepted solution]

data NEW;

input CLIENTNAME$ VERSION;

cards;

Client1 8.1

Client1 8.2

Client2 8.1

Client2 8.2

Client3 8.1

Client4 8.1

Client5 8.1

;

run;

proc transpose data=NEW out=OUT1;

  var VERSION;

  by CLIENTNAME;

run;

options missing="";

data OUT2;

  length VERSION2 $20.;

  set OUT1;

  VERSION2 = catx("," , of COL:);

run;

options missing=".";