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.
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)
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?
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
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;
You probably miised the line: if last.k3 then output
otherwise post the code you used.
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!
@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?
@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.
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;
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;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.