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 |
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;
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;
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;
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
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.