topic Summary function in SAS Programming

Transpose

vedant — Mon, 09 Jan 2017 06:04:43 GMT

This is the input.

data asd;
input pol_key polsyskey Qutokey Predicted Month Addition;
datalines;
1 2 3 0.5 1 1
1 2 3 4.5 2 1
1 2 3 10.9 3 1
;
run;

OUTPUT REQUIRED

pol_key polsyskey Qutokey Pr1 Pr2 Pr3 Add1 Add2 Add3

1 2 3 0.5 4.5 10.9 1 1 1

How do I get this output? Please help.

Summary function

vedant — Mon, 09 Jan 2017 06:09:10 GMT

data asd;
input pol_key polsyskey Qutokey Predicted ;
datalines;
1 2 3 0.5
1 2 3 4.5
1 2 3 10.9
;
run;

OUTPUT

pol_key pol_syskey Qutokey Pr1 Pr2 Pr3

1 2 3 0.5 4.5 10.9

How to get this output using Summary function? (Not by Transpose function)

Re: Transpose

mnjtrana — Mon, 09 Jan 2017 06:35:13 GMT

Don't you think, this is very same as your other post, - Summary function

https://communities.sas.com/t5/Base-SAS-Programming/Summary-function/m-p/323259#M71628

Can you please delete one of them, they are duplicate.

Thanks,

Manjeet

Re: Transpose

Shmuel — Mon, 09 Jan 2017 06:48:42 GMT

What is the max occurences of same 3-key variables can be ?

Here is the code to be done. Just rename variables to yours:

data test;
     input k1 k2 k3 a b c;
datalines;
1 2 3 0.5 1 1
1 2 3 4.5 2 1
1 2 3 10.9 3 1
; run;

%let maxarray = 3;

data want;
 set test;
  by k1 k2 k3;
     retain i pr1-pr&maxarray ad1-ad&maxarray;
     array prx {&maxarray} pr1-pr&maxarray;
     array adx {&maxarray} ad1-ad&maxarray;
     if first.k3 then i=1; else i+1;
  
     prx(i) = a;
     adx(i) = c;
     if last.k3 then output;
     drop i a b c;
run;

Re: Transpose

vedant — Mon, 09 Jan 2017 06:55:14 GMT

Well, I got the output by one way but it can be optimized (I think so). Here it is-

data asd;
input pol_key polsyskey Qutokey Predicted Month Addition;
datalines;
1 2 3 0.5 1 1
1 2 3 4.5 2 1
1 2 3 10.9 3 1
;
run;

data subtab (keep=addition);
set asd;
run;

proc transpose data=subtab
out=ftab;
run;

data qwe2;
set ftab(rename=(COL1=A1 COL2=A2 COL3=A3)drop=_NAME_ );
run;

data subtab1 (drop=Month Addition);
set asd;
run;

proc transpose data=subtab1
out=qwe;
by pol_key polsyskey Qutokey ;
run;

data qwe1;
set qwe(rename=(COL1=Prd1 COL2=Prd2 COL3=Prd3)drop=_NAME_ );
run;

DATA final;
merge qwe1 qwe2 ;
RUN;

Re: Transpose

vedant — Mon, 09 Jan 2017 06:59:50 GMT

Hi mnjtrana,

Yes, I know the output of both questions are similar,but I would like the same query to be solved by using PROC SUMMARY.

Thanks!

Re: Summary function

LinusH — Mon, 09 Jan 2017 07:01:18 GMT

First, why use a constraint in your question?
Isn't it most important to solve the problem?
I can't see any summing in your data example so why would you like a sum function to do this? It is a transpose.

Re: Transpose

vedant — Mon, 09 Jan 2017 07:05:02 GMT

Hi Shmuel,

I ran your code, but it's not the required output. It needs to be in a single line just like the output given in the query.

Thanks.

Re: Transpose

Shmuel — Mon, 09 Jan 2017 07:11:56 GMT

You probably miised the line: if last.k3 then output

otherwise post the code you used.

Re: Transpose

Kurt_Bremser — Mon, 09 Jan 2017 07:49:18 GMT

@mnjtrana wrote:

Don't you think, this is very same as your other post, - Summary function

https://communities.sas.com/t5/Base-SAS-Programming/Summary-function/m-p/323259#M71628

Can you please delete one of them, they are duplicate.

Thanks,

Manjeet

Fixed that by merging into one thread.

Re: Summary function

ballardw — Mon, 09 Jan 2017 16:29:10 GMT

OUTPUT

pol_key pol_syskey Qutokey Pr1 Pr2 Pr3

1 2 3 0.5 4.5 10.9

How to get this output using Summary function? (Not by Transpose function)

Basically Not going to happen with Proc Summary without post or pre processing involving something like transpose. Proc summary will create summary statistics for specified varaibles within each combination of class variables (or specified subset of them). I could likely get the specific result for a very small set of data such as you show with specific quantiles of your predicted variable but it would almost certainly not be a generaly solution. If you data will ONLY EVER have EXACTLY 3 values for predicted for pol_key polsyskey Qutokey it is possible. Is that going to be the case in your real project?

Re: Transpose

Kurt_Bremser — Mon, 09 Jan 2017 16:32:44 GMT

@vedant wrote:

Hi mnjtrana,

Yes, I know the output of both questions are similar,but I would like the same query to be solved by using PROC SUMMARY.

Thanks!

You want to transpose without using proc transpose? Are you also trying to walk without using your feet?

Re: Transpose

ballardw — Mon, 09 Jan 2017 17:28:28 GMT

Similar idea but using more of the transpose options to clean things up:

data asd;
   input pol_key polsyskey Qutokey Predicted Month Addition;
datalines;
1 2 3 0.5 1 1
1 2 3 4.5 2 1
1 2 3 10.9 3 1
;
run;
proc sort data=asd;
   by pol_key polsyskey Qutokey;
run;

proc transpose data=asd out=mtrans (drop=_name_)
   prefix=P;
   by pol_key polsyskey Qutokey;
   var predicted;
   id month;
run;
proc transpose data=asd out=atrans (drop=_name_)
   prefix=Add;
   by pol_key polsyskey Qutokey;
   var Addition;
   id month;
run;

data want;
   merge mtrans atrans;
   by pol_key polsyskey Qutokey;
run;