BookmarkSubscribeRSS Feed
vedant
Calcite | Level 5

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.

12 REPLIES 12
vedant
Calcite | Level 5

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)

LinusH
Tourmaline | Level 20
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
ballardw
Super User

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?

mnjtrana
Pyrite | Level 9

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
Shmuel
Garnet | Level 18

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;
vedant
Calcite | Level 5
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.
Shmuel
Garnet | Level 18

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

 

otherwise post the code you used.

vedant
Calcite | Level 5

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!

Kurt_Bremser
Super User

@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?

vedant
Calcite | Level 5

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;

 

ballardw
Super User

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;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 12 replies
  • 1586 views
  • 3 likes
  • 6 in conversation