DATA Step, Macro, Functions and more

Transpose

Reply
Occasional Contributor
Posts: 6

Transpose

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.

Occasional Contributor
Posts: 6

Summary function

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)

Super User
Posts: 5,426

Re: Summary function

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.
Data never sleeps
Super User
Posts: 11,343

Re: Summary function


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?

Contributor
Posts: 52

Re: Transpose

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


Cheers from India!

Manjeet
Trusted Advisor
Posts: 1,555

Re: Transpose

[ Edited ]

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;
Occasional Contributor
Posts: 6

Re: Transpose

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

Re: Transpose

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

 

otherwise post the code you used.

Occasional Contributor
Posts: 6

Re: Transpose

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!

Super User
Posts: 7,766

Re: Transpose


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?

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Super User
Posts: 7,766

Re: Transpose


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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Occasional Contributor
Posts: 6

Re: Transpose

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;

 

Super User
Posts: 11,343

Re: Transpose

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;
Ask a Question
Discussion stats
  • 12 replies
  • 431 views
  • 3 likes
  • 6 in conversation