Prev: Partial Nesting in Proc Mixed - Follow Up
Next: Proc SQL 'Or' statement use
From: Sravan Varma on 26 Jan 2010 12:03
Hi ,
I have a dataset with records like

ID VI Var1 Var2 Var3
1 1 . 23 .
1 1 21 . .
1 1 . .
24

I want this to be in one row something like

ID VI Var1 Var2 Var3
1 1 21 23 24


Any suggestion on how to make this possible?

Appreciate your time and help.

Regards
Matt
From: data _null_; on 26 Jan 2010 12:08
On Jan 26, 11:03 am, Sravan Varma <clinicalprogrammer....(a)gmail.com>
wrote:
> Hi ,
> I have a dataset with records like
>
> ID VI Var1 Var2 Var3
> 1   1    .      23      .
> 1   1   21      .       .
> 1   1    .        .
> 24
>
> I want this to be in one row something like
>
> ID VI Var1 Var2 Var3
> 1   1   21     23    24
>
> Any suggestion on how to make this possible?
>
> Appreciate your time and help.
>
> Regards
> Matt
UPDATE statement....

data have;
input (ID VI)(:$1.) Var1-Var3;
cards;
1 1 . 23 .
1 1 21 . .
1 1 . . 24
;;;;
run;
data flat;
update have(obs=0) have;
by id vi;
run;
proc print;
run;
From: Eddie on 26 Jan 2010 14:37
On Jan 26, 12:08 pm, "data _null_;" <datan...(a)gmail.com> wrote:
> On Jan 26, 11:03 am, Sravan Varma <clinicalprogrammer....(a)gmail.com>
> wrote:
>
>
>
> > Hi ,
> > I have a dataset with records like
>
> > ID VI Var1 Var2 Var3
> > 1   1    .      23      .
> > 1   1   21      .       .
> > 1   1    .        .
> > 24
>
> > I want this to be in one row something like
>
> > ID VI Var1 Var2 Var3
> > 1   1   21     23    24
>
> > Any suggestion on how to make this possible?
>
> > Appreciate your time and help.
>
> > Regards
> > Matt
>
> UPDATE statement....
>
> data have;
>    input (ID VI)(:$1.) Var1-Var3;
>    cards;
> 1   1    .      23      .
> 1   1   21      .       .
> 1   1    .        .  24
> ;;;;
>    run;
> data flat;
>    update have(obs=0) have;
>    by id vi;
>    run;
> proc print;
>    run;

Interesting to see this approach. I was expecting either a data step
or Proc Transpose ..... Thanks...

Matt
From: Mike Zdeb on 26 Jan 2010 15:19
not just interesting, but elegant !!!

another way (not as elegant, but data need not be sorted by ID and VI) ...

data have;
input (ID VI)(:$1.) Var1-Var3;
cards;
1 1 . 23 .
1 1 21 . .
1 1 . . 24
;
run;

proc summary data=have nway;
class id vi;
var var1-var3;
output out=want (drop=_:) sum=;
run;

--
Mike Zdeb
U(a)Albany School of Public Health
One University Place
Rensselaer, New York 12144-3456
P/518-402-6479 F/630-604-1475

> On Jan 26, 12:08 pm, "data _null_;" <datan...(a)gmail.com> wrote:
>> On Jan 26, 11:03 am, Sravan Varma <clinicalprogrammer....(a)gmail.com>
>> wrote:
>>
>>
>>
>> > Hi ,
>> > I have a dataset with records like
>>
>> > ID VI Var1 Var2 Var3
>> > 1 1 . 23 .
>> > 1 1 21 . .
>> > 1 1 . .
>> > 24
>>
>> > I want this to be in one row something like
>>
>> > ID VI Var1 Var2 Var3
>> > 1 1 21 23 24
>>
>> > Any suggestion on how to make this possible?
>>
>> > Appreciate your time and help.
>>
>> > Regards
>> > Matt
>>
>> UPDATE statement....
>>
>> data have;
>> input (ID VI)(:$1.) Var1-Var3;
>> cards;
>> 1 1 . 23 .
>> 1 1 21 . .
>> 1 1 . . 24
>> ;;;;
>> run;
>> data flat;
>> update have(obs=0) have;
>> by id vi;
>> run;
>> proc print;
>> run;
>
> Interesting to see this approach. I was expecting either a data step
> or Proc Transpose ..... Thanks...
>
> Matt
>
From: Sigurd Hermansen on 26 Jan 2010 21:59
Clever. My only qualm would be uncertainty about results in slightly more complex situations. The SQL GROUP BY alternative removes some of that uncertainty when some columns have multiple non-missing values:
data have;
input (ID VI)(:$1.) Var1-Var3;
cards;
1 1 . 23 .
1 1 21 . .
1 1 20 . 24
;;;;
run;
data flat;
update have(obs=0) have;
by id vi;
run;
proc print;
run;
proc sql;
select distinct ID,VI,max(Var1) as Var1,max(Var2) as Var2,max(Var3) as Var3
from have
group by ID,VI
;
quit;

-----Original Message-----
From: SAS(r) Discussion [mailto:SAS-L(a)LISTSERV.UGA.EDU] On Behalf Of data _null_;
Sent: Tuesday, January 26, 2010 12:08 PM
To: SAS-L(a)LISTSERV.UGA.EDU
Subject: Re: duplicate records data into single row

On Jan 26, 11:03 am, Sravan Varma <clinicalprogrammer....(a)gmail.com>
wrote:
> Hi ,
> I have a dataset with records like
>
> ID VI Var1 Var2 Var3
> 1 1 . 23 .
> 1 1 21 . .
> 1 1 . .
> 24
>
> I want this to be in one row something like
>
> ID VI Var1 Var2 Var3
> 1 1 21 23 24
>
> Any suggestion on how to make this possible?
>
> Appreciate your time and help.
>
> Regards
> Matt
UPDATE statement....

data have;
input (ID VI)(:$1.) Var1-Var3;
cards;
1 1 . 23 .
1 1 21 . .
1 1 . . 24
;;;;
run;
data flat;
update have(obs=0) have;
by id vi;
run;
proc print;
run;
 |  Next  |  Last
Pages: 1 2
Prev: Partial Nesting in Proc Mixed - Follow Up
Next: Proc SQL 'Or' statement use