topic Re: Input with unique ID in SAS Programming

Input with unique ID

yuen68 — Sat, 06 Jul 2013 17:40:13 GMT

Here is the raw dataset,

ID value

A 1

A 2

A 3

B 1

B 2

B 3

C 1

C 2

C 3

Expected output:

ID Var1 Var2 Var3

A 1 2 3

B 1 2 3

C 1 2 3

How could I make it by one data step? I have tried array but still cant figure out the correct assignment.... May there be any hints on inputting data like this?

Million thankss.......

Re: Input with unique ID

Tom — Sat, 06 Jul 2013 18:37:52 GMT

If you already have the data use PROC TRANSPOSE

proc transpose data=have prefix=var out=want ;

by id ;

var value;

run;

Re: Input with unique ID

yuen68 — Sat, 06 Jul 2013 18:52:43 GMT

Thanks for your suggestion Tom.

May I use a single data step to do so except the proc transpose?

Re: Input with unique ID

Tom — Sat, 06 Jul 2013 19:36:29 GMT

There are two problems with trying to do this with a data step.

1) How large to make the array. You could just set an upper limit to get the program to run.

2) How will the data step know when you have reached the end of the list of values for the current ID.

It might be easier to make a view to read the data and then call PROC TRANSPOSE or another data step to roll the data up.

data have / view=have ;

infile sample ;

input id $ value ;

run;

proc transpose data=have out=want prefix=var ;

by id;

var value ;

run;

data want ;

do i=1 by 1 until (last.id);

set have ;

by id;

array var (4); * What value to use for size of the array??? ;

var(i)=value ;

end;

drop i value ;

run;

You could do it in a single data step, but you will need to have the data in separate file so that you can tell when you have reached the end to enable outputting the last observation. (note there is a trick with parmcards that can get around this if you must).

data want;

infile sample end=eof truncover ;

input @1 next $ @@;

id = next ;

array var (4) ;

do i=1 by 1 until (next ne id ) ;

input value ;

var(i) = value;

if not eof then input next $ @@ ;

else next = ' ';

end;

output;

drop next value i ;

run;

Re: Input with unique ID

Ksharp — Sun, 07 Jul 2013 01:22:42 GMT

You can refer to my and Arthur.T's paper at SGF , which demonstrate how to use data step instead of proc transpose :

http://support.sas.com/resources/papers/proceedings13/517-2013.pdf
http://support.sas.com/resources/papers/proceedings13/538-2013.pdf

 

 
data have;
input ID $ value ;
cards;
A 1
A 2
A 3
B 1
B 2
B 3
C 1
C 2
C 3
;
run;
proc sql noprint;
 select max(value) into : n from have;
quit;
data want(keep=id var:);
 set have;
 by id;
 array v{*} var1-var%left(&n);
 retain var:;
 v{value}=value;
 if last.id then do; output; call missing(of v{*});end;
run;

Ksharp

Message was edited by: xia keshan

Re: Input with unique ID

yuen68 — Sun, 07 Jul 2013 05:33:29 GMT

Hi Tom,

what if it's given that there are three fixed ID (i.e. A,B,C) but unknown numbers of var? Will it be possible to use the array + do loop to complete it?

Re: Input with unique ID

Patrick — Sun, 07 Jul 2013 07:22:30 GMT

Hi Ksharp

I was looking forward to reading this paper which Art once mentioned in a post. Thanks for posting the link.

Cheers

Patrick

Re: Input with unique ID

Ksharp — Sun, 07 Jul 2013 07:34:08 GMT

You are welcome. Actually , those papers were written almost by Arthur.T . I even have no time to review these papers and give some good advices.

Sorry , Arthur.T !

Ksharp

Message was edited by: xia keshan

Re: Input with unique ID

LillianLee — Sun, 07 Jul 2013 14:47:10 GMT

Data work;

input ID $ Var1 @

/ @3 Var2 @

/ @3 Var3;

Cards;

A 1

A 2

A 3

B 1

B 2

B 3

C 1

C 2

C 3

;

run;

Re: Input with unique ID

NagendraKumarK — Mon, 08 Jul 2013 15:17:35 GMT

data hi;
input id$ val1 /
id$ val2 /
id$ val3;

datalines;
A 1
A 2
A 3
B 1
B 2
B 3
C 1
C 2
C 3
;
run;