sas enquiry

[SalmaTas]

i would like to get the result found below if the input was the table below, attached is an excel sheet for easier access to the data

Input Example
ID	Card	Usage	Limit
123	1111	50	100
123	1112	10	20
123	1113	15	50
456	2221	40	50
456	2222	0	10

Output Required
ID	Card1	Usage1	Limit1	Card2	Usage2	Limit2	Card3	Usage3	Limit3
123	1111	50	100	1112	10	20	1113	15	50
456	2221	40	50	2222	0	10	0	0	0

[Jagadishkatam · Accepted solution]

please try

data have;
input ID 	Card 	Usage 	Limit;
cards;
123 	1111 	50 	100
123 	1112 	10 	20
123 	1113 	15 	50
456 	2221 	40 	50
456 	2222 	0 	10
;

data have;
set have;
by id;
if first.id then count=1;
else count+1;
run;

options missing=0;
data want;
set have;
by id;
retain Card1- Card3 Usage1- Usage3 Limit1- Limit3;
array nc(3) Card1- Card3;
array nu(3) Usage1- Usage3;
array nl(3) Limit1- Limit3;
if first.id then do;
do i = 1 to 3;
nc(i)=.;
nu(i)=.;
nl(i)=.;
end;
end;
nc(count)=card;
nu(count)=usage;
nl(count)=limit;
if last.id;
drop i;
run;

[SalmaTas]

thanks a lot, worked perfectly

[RW9]

Just to note, simple tranposes can be used here, if you had lots of variables it may be a simpler method than numbering and each item:

data have;
  input ID 	Card 	Usage 	Limit;
cards;
123 	1111 	50 	100
123 	1112 	10 	20
123 	1113 	15 	50
456 	2221 	40 	50
456 	2222 	0 	10
;
run;

proc transpose data=have out=card prefix=card;
  by id;
  var card;
run;
proc transpose data=have out=usage prefix=usage;
  by id;
  var usage;
run;
proc transpose data=have out=limit prefix=limit;
  by id;
  var limit;
run;
data want (drop=_name_);
  merge card usage limit;
  by id;
run;

[SalmaTas]

great, thanks a lot

[Ksharp]

The simplest way is using proc summary + idgroup . 

data have;
input ID 	Card 	Usage 	Limit;
cards;
123 	1111 	50 	100
123 	1112 	10 	20
123 	1113 	15 	50
456 	2221 	40 	50
456 	2222 	0 	10
;
run;

proc sql noprint;
 select max(n) into : n
  from (select count(*) as n from have group by id);
quit;
proc summary data=have;
by id;
output out=want idgroup(out[&n] (Card 	Usage 	Limit)= );
run;

If you have big table , check Merge Skill :

http://support.sas.com/resources/papers/proceedings15/2785-2015.pdf