Hello,
Below is the input
id count qty Amount
1P6743 9 1 241.98
1P6743 9 50 5.64000
1P6743 9 83 5.43000
1P6743 9 100 5.38000
1P6743 9 150 5.30000
I need a output like
id count qty Amount qty Amount qty Amount qty Amount qty Amount
1P6743 9 1 241.98 50 5.64000 83 5.43000 100 5.38000 150 5.30000
How we can get above result ?
Try next code, assuming data is sorted by id count:
%let maxocc = 10; /* adapt to max number of occurrences */
data want;
set have;
by id count;
retain qt1-qt&maxocc amt1-amt&maxocc i;
drop i;
if first.count then do;
call missing(qt1-qt&maxocc, amt1-amt&maxocc);
i=0;
end;
array qx qt1-qt&maxocc;
array ax amt1-amt&maxocc;
i+1;
qx(i) = qty;
ax(i) = amount;
if last.count then output;
run;
You my try also proc transpose; Check SAS Documentation on PROC TRANSPOSE.
Getting Below Error.
call missing(qt1-qt&maxocc, amt1-amt&maxocc);
_______
134
WARNING 134-185: Argument #1 is an expression, which cannot be updated by the MISSING subroutine call.
call missing(qt1-qt&maxocc, amt1-amt&maxocc);
_______
134
WARNING 134-185: Argument #2 is an expression, which cannot be updated by the MISSING subroutine c
ALWAYS (and I do mean ALWAYS) use the {i} button for posting logs, as this will preserve the positions of ERROR pointers and other valuable log formatting.
I have no that error on SAS 9.4 (SAS UE), anyhow here is a tested code that may run on your SAS version:
data have;
infile datalines;
input id $ count qty Amount best.;
datalines;
1P6743 9 1 241.98
1P6743 9 50 5.64000
1P6743 9 83 5.43000
1P6743 9 100 5.38000
1P6743 9 150 5.30000
; run;
%let maxocc = 10; /* adapt to max number of occurrences */
data want;
set have;
by id count;
retain qt1-qt&maxocc amt1-amt&maxocc i;
array qx qt1-qt&maxocc;
array ax amt1-amt&maxocc;
drop i;
if first.count then do;
do i=1 to &maxocc; qx(i)=.; ax(i)=.; end;
*call missing(qt1-qt&maxocc, amt1-amt&maxocc); /* alternate code work on SAS 9.4 */
i=0;
end;
i+1;
qx(i) = qty;
ax(i) = amount;
if last.count then output;
run;
@jaiganesh wrote:
Getting Below Error.
call missing(qt1-qt&maxocc, amt1-amt&maxocc);
_______
134
WARNING 134-185: Argument #1 is an expression, which cannot be updated by the MISSING subroutine call.
Hi @jaiganesh,
As a rule, you need to use the OF keyword when applying functions or CALL routines to variable lists:
call missing(of qt1-qt&maxocc, of amt1-amt&maxocc);
If you don't have a big table.
data have;
infile datalines;
input id $ count qty Amount best.;
datalines;
1P6743 9 1 241.98
1P6743 9 50 5.64000
1P6743 9 83 5.43000
1P6743 9 100 5.38000
1P6743 9 150 5.30000
; run;
proc sql noprint;
select max(n) into : n
from (select count(*) as n from have group by id,count);
quit;
proc summary data=have;
by id count;
output out=want idgroup(out[&n] (qty amount)=);
run;
Important question:
Do you need a data set result, further computer manipulation, or a report that people will read?
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!
SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.
Find more tutorials on the SAS Users YouTube channel.