## sas enquiry

Solved
Occasional Contributor
Posts: 8

# sas enquiry

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

Accepted Solutions
Solution
‎12-14-2015 04:27 AM
Posts: 1,147

## Re: sas enquiry

``````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;
``````
Thanks,
Jag

All Replies
Solution
‎12-14-2015 04:27 AM
Posts: 1,147

## Re: sas enquiry

``````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;
``````
Thanks,
Jag
Occasional Contributor
Posts: 8

## Re: sas enquiry

thanks a lot, worked perfectly
Super User
Posts: 9,599

## Re: sas enquiry

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;```
Occasional Contributor
Posts: 8

## Re: sas enquiry

great, thanks a lot
Super User
Posts: 10,784

## Re: sas enquiry

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

🔒 This topic is solved and locked.