SAS Program

[jaiganesh]

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 ?

[Shmuel]

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.

[jaiganesh]

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

[Kurt_Bremser]

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.

[Shmuel]

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;     

[FreelanceReinh]

---

@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);

[Ksharp]

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;

[ballardw]

Important question:

Do you need a data set result, further computer manipulation, or a report that people will read?