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.......
If you already have the data use PROC TRANSPOSE
proc transpose data=have prefix=var out=want ;
by id ;
var value;
run;
Thanks for your suggestion Tom.
May I use a single data step to do so except the proc transpose?
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;
or
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;
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?
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
Hi Ksharp
I was looking forward to reading this paper which Art once mentioned in a post. Thanks for posting the link.
Cheers
Patrick
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
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;
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.