Hi All,
I have a dataset and a simple request to combine two specific rows together, as follows:
Dataset: Loans_2016
Category Gross Net Reg Loss
home 300 200 50 25
RV 100 80 15 5
TDR 80 75 5 3
HL1 70 61 1 1
Bad_HL1 42 30 5 .
HL2 88 70 12 6
Bad_HL2 35 28 7 .
I just want to add Bad_HL1 into HL1 and add Bad_HL2 into HL2, (and drop those rows so that only home RV TDR HL1 HL2 are displayed.
I have done everything else to get my data but I can't figure out how to do this concatenation with the display I want to see in the end result. Could anyone please provide me with any code help you can?
Thank you very much!
This works:
proc sql;
create table loans as
select case when upcase(Category) eqt "BAD_" then substr(Category,5) else Category end as Category,
sum(Gross) as Gross, sum(Net) as Net, sum(Reg) as Reg, sum(Loss) as Loss
from loans_2016
group by calculated Category;
select * from loans;
quit;
proc sql;
select distinct prxchange('s/\w+_//',1,category),sum(gross) as gross, sum(Net) as Net,sum(Reg) as Reg,sum(Loss) as Loss
from have
group by prxchange('s/\w+_//',1,category);
quit;
Hi slchen,
thanks for your reply! after running the code unfortunately, it works great, but I can't get it to apply to my full expanded dataset (below). I see that you used the "_" as the key indicator to do the concatenation but can you advise on how to change your code when the "categoy" is expanded:
Full dataset:
Category Gross Net Reg Loss
FM_AR
FM_IO
HEIL
HEL_AM
HRND
HEL_IO_1
HR Balloon
LR Bast
Other_HEL IO
FM_TRD
HE_TRD
BAD_HEIL
BAD_HELOC_MO
RV
MAR_L
These are my full rep categories (gross through loss contain numeric values, so we can assume it's properly imported in SAS). Going off of your code, can you help me ammended such that BAD_HEIL is added into HEIL and Bad_HELOC_MO is added to HR Balloon? Then the rows with those BAD_ categories are eliminated from the dataset.
Sorry I did not have this expanded dataset originially, I thought I could build upon yours but I now see it's more complicated than it looks. Thank you very much.
Thank you for your insight!
This works:
proc sql;
create table loans as
select case when upcase(Category) eqt "BAD_" then substr(Category,5) else Category end as Category,
sum(Gross) as Gross, sum(Net) as Net, sum(Reg) as Reg, sum(Loss) as Loss
from loans_2016
group by calculated Category;
select * from loans;
quit;
Yes, I am sure
I remove BAD_ from the Category and group on what's left, i.e. one line with the original category name and the other with the left-truncated name.
When the Category name does not start wih BAD_, it is kept as is (this is the second clause of the case-expression). The initial order of categories does not matter. SQL does its own sort on the left-truncated names to perform the grouping.
The clause group by calculated Category refers to the truncated names, i.e. the result of the case-expression.
Thank you very much for your many insights and conceptual breakdown PG!
data have;
input Category $ Gross Net Reg Loss;
cards;
home 300 200 50 25
RV 100 80 15 5
TDR 80 75 5 3
HL1 70 61 1 1
Bad_HL1 42 30 5 .
HL2 88 70 12 6
Bad_HL2 35 28 7 .
;
run;
data want;
merge have have(firstobs=2 rename=(
Category=_C Gross=_G Net=_N Reg=_R Loss=_L));
if Category=:'HL' then do;
Gross=sum(Gross,_G); Net=sum(Net,_N);
Reg=sum(Reg,_R); Loss=sum(Loss,_L);
end;
if Category=:'Bad_HL' then delete;
drop _:;
run;
This works great and is straightforward, Thank you Xia!
Sounds like a simple join to me:
data have; input Category $ Gross Net Reg Loss; datalines; home 300 200 50 25 RV 100 80 15 5 TDR 80 75 5 3 HL1 70 61 1 1 Bad_HL1 42 30 5 . HL2 88 70 12 6 Bad_HL2 35 28 7 . ; run; proc sql; create table WANT as select A.CATEGORY, sum(A.GROSS,B.GROSS) as GROSS, sum(A.NET,B.NET) as NET, sum(A.REG,B.REG) as REG, sum(A.LOSS,B.LOSS) as LOSS from (select * from HAVE where upcase(substr(CATEGORY,1,4)) ne "BAD_") A left join (select * from HAVE where upcase(substr(CATEGORY,1,4))="BAD_") B on A.CATEGORY=substr(B.CATEGORY,4); quit;
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.
Select SAS Training centers are offering in-person courses. View upcoming courses for: