topic Re: SAS Program in New SAS User

SAS Program

jaiganesh — Fri, 26 Jul 2019 08:47:38 GMT

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 ?

Re: SAS Program

Shmuel — Fri, 26 Jul 2019 09:15:46 GMT

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.

Re: SAS Program

jaiganesh — Fri, 26 Jul 2019 09:35:14 GMT

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

Re: SAS Program

Kurt_Bremser — Fri, 26 Jul 2019 10:08:32 GMT

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.

Re: SAS Program

Shmuel — Fri, 26 Jul 2019 10:30:26 GMT

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;

Re: SAS Program

Ksharp — Fri, 26 Jul 2019 11:58:08 GMT

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;

Re: SAS Program

FreelanceReinh — Fri, 26 Jul 2019 13:50:17 GMT

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

Re: SAS Program

ballardw — Fri, 26 Jul 2019 15:19:52 GMT

Important question:

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