Hi,
I'm new to SAS (using SAS 9.4) and the community.
I have the following data, and I want to roll up some of the table.
WOT Nb Mbr Type
A 5 100 PYGT
B 2 20 PYGM
C 1 3 CLAS
D 20 400 FGP
I want
WOT Nb Mbr Type
A_B 7 127 PYG
C 1 3 CLAS
D 20 400 FGP
Is there a way to code for this?
Thank you.
Is that ALL the data or just a sample?
I ask because there are likely rules involved that you have not stated such as why does the value of Type get shorter and what would the generic rule be to do set the new values.
I might "guess" that your new value for MBR is the sum of MBR and NB for the "rolled up" group but you did not say so and my approach might not work for other data if that is not exactly the rule to be applied to any other values of WOT that are to be "rolled up".
Hi,
yes that is just a sample. There are only a few rows, out of about 30, that I need to roll/sum up the others stay as is.
The Type is just a going to be description of what they are, the length really doesn't mean anything.
shoot, that's a typo. It should be 120 not 127!
Okay, you told us it is a sample. You haven't told us the rules to use when this "rolling up" happens.
By the way, does "rolling up" mean "sum"?
The only rules are when there is an A and B they go together, or the other group will be G, P and W, all the other letters stay on their own.
I'm so sorry, I'm not so good at explaining what I need. I am so new to this.
And yes I guess rolling up means to sum the numerical values.
Maybe I should delete my post? 😞
@SASuser412 wrote:
The only rules are when there is an A and B they go together, or the other group will be G, P and W, all the other letters stay on their own.
I'm so sorry, I'm not so good at explaining what I need. I am so new to this.
And yes I guess rolling up means to sum the numerical values.
Maybe I should delete my post? 😞
Live and learn.
By "A and B" together do you mean that you have multiple "sets" of A B C D and that the records need to be processed in existing order? Such as:
WOT Nb Mbr Type A 5 100 PYGT B 2 20 PYGM C 1 3 CLAS D 20 400 FGP A 1 80 PYGT B 3 10 PYGM C 1 3 CLAS D 20 400 FGP to: WOT Nb Mbr Type A-B 7 120 PYG C 1 3 CLAS D 20 400 FGP A-B 4 90 PYG C 1 3 CLAS D 20 400 FGP
I would need the result to be more like this
From:
WOT Nb Mbr Type A 5 100 PYGT B 2 20 PYGM C 1 3 CLAS D 20 400 FGP E 1 80 TPEO F 3 10 TPEM G 1 3 WCQ H 20 400 VXZ
I 6 140 DIX
...
Z 30 600 YNR
TO:
WOT Nb Mbr Type
A-B 7 120 PYG
C 1 3 CLAS
D 20 400 FGP
E-F 4 90 TPE
G 1 3 WCQ
H 20 400 VXZ
I 6 140 DIX
...
Z 30 600 YNR
You want to combine WOT A (PYGT) and WOT B (PYGM), producing WOT=A-B (PYG).
And you want to combine WOT E (TPEO) and WOT F (TPEM), producing WOT=E-F (TPE)
It appears you want to collapse all 4-character type's that happen to share the first 3 letters.
Is that the general rule?
Yes, thank you for clearly stating that for me.
One way:
data have; input WOT $ Nb Mbr Type $; datalines; A 5 100 PYGT B 2 20 PYGM C 1 3 CLAS D 20 400 FGP E 1 80 TPEO F 3 10 TPEM G 1 3 WCQ H 20 400 VXZ I 6 140 DIX Z 30 600 YNR ; data temp; set have; if wot in ('A' 'B' 'E' 'F') then type=substr(type,1,3); run; proc format; value $wotgroup 'A','B' = 'A-B' 'E','F' = 'E-F' ; proc summary data=temp nway; class wot type; format wot $wotgroup.; var nb mbr; output out=want (drop=_:) sum=; run;
The first data set just provides something to code against.
The second uses the apparent rule to trim the Type to 3 characters for some values. This might need to be changed for more complex "rules". If there is only one value to be associated with the WOT (grouped or not) then another approach would be create a second format.
The Proc format creates groups for values of WOT. The groups created with formats will be honored by analysis, reporting and most graphing procedures (exceptions are mostly custom date/time/datetime formats).
Proc summary with CLASS creates groups of values, by could be used as well. The output requests the sum of the variables for the grouped WOT / Type combinations.
Caution: The underlying values in the WOT variable is still extremely likely to be 'A' and 'E' but the format will display the text as shown.
This actually can overcome some issues not previously addressed. If your WOT variable is only one character in length there is no way to stuff 'A-B' into it and more work involving creating a longer variable would be needed. The Format will display just about any text you want for the value. Which is another option of using a format for the TYPE as well to trim the displayed value.
Or possible create reports from the Temp data set:
proc tabulate data=temp; class wot type; format wot $wotgroup.; var nb mbr; table wot*type, (nb mbr)* sum=' '*f=best5. ; run;
proc report data=temp; columns wot nb mbr type; define wot /group format=$wotgroup.; define type/group; run;
Thank you, I'm going to try these!
And the WOT variable can be more than one character.
... and where does the 127 come from in your output?
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.