topic Re: Two rows data into one row in SAS Data Management

Two rows data into one row

NareshAbburi — Tue, 11 Nov 2014 21:41:16 GMT

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;

Re: Two rows data into one row

Patrick — Tue, 11 Nov 2014 21:54:46 GMT

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

Re: Two rows data into one row

NareshAbburi — Tue, 11 Nov 2014 21:59:58 GMT

Hi,

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

could you please explain me..?

Re: Two rows data into one row

Reeza — Tue, 11 Nov 2014 22:18:23 GMT

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;

Re: Two rows data into one row

stat_sas — Tue, 11 Nov 2014 23:41:16 GMT

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;

Re: Two rows data into one row

amats — Wed, 12 Nov 2014 03:40:38 GMT

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=".";

Re: Two rows data into one row

NareshAbburi — Wed, 12 Nov 2014 15:01:33 GMT

Hi,

Thanks for your reply.

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