DATA Step, Macro, Functions and more

sas enquiry

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 8
Accepted Solution

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
Trusted Advisor
Posts: 1,137

Re: sas enquiry

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

View solution in original post


All Replies
Solution
‎12-14-2015 04:27 AM
Trusted Advisor
Posts: 1,137

Re: sas enquiry

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

Re: sas enquiry

Posted in reply to Jagadishkatam
thanks a lot, worked perfectly
Super User
Super User
Posts: 7,992

Re: sas enquiry

Posted in reply to Jagadishkatam

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,044

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.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 428 views
  • 3 likes
  • 4 in conversation