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
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.