The example table i have is:
Var1 Var2 Var3 Var4 Var5
Gender East
Male 25 28 30 East
Female 12 45 14 East
Gender West
Male 10 5 4 West
Female 4 14 3 West
I need to create another values as below
Var1 Var2 Var3 Var4 Var5
Gender East
Male 25 28 30 East
Female 12 45 14 East
Gender West
Male 10 5 4 West
Female 4 14 3 West
Gender East-West
Male 35 33 34 East-West
Female 16 59 17 East-West
What is it for ?
Var1 Var2 Var3 Var4 Var5
Gender East
Male 25 28 30 East
Female 12 45 14 East
Gender 14 25 10 West
Male 10 5 4 West
Female 4 14 3 West
You want a table or a report ?
data have;
input Var1 $ Var2 Var3 Var4 Var5 : $20.;
cards;
Gender . . . East
Male 25 28 30 East
Female 12 45 14 East
Gender . . . West
Male 10 5 4 West
Female 4 14 3 West
;
run;
proc sql;
create table want(drop=group) as
select 1 as group, * from have
union
select 2 as group,var1,sum(var2) as var2,sum(var3) as var3,sum(var4) as var4,'East-West' as var5 length=20
from have
group by var1
order by group,var5;
quit;
Thank you, it works for this example. But i have more then that. In my real work i t doesn';t work. I have more groups. So should i have
Var1 Var2 Var3 Var4 Var5
Gender East
Male 25 28 30 East
Female 12 45 14 East
Gender West
Male 10 5 4 West
Female 4 14 3 West
Gender 5 1 2 Osceola
Female 1 74 21 Osceola
Male 41 25 32 Osceola
Is that the same?
data have;
input Var1 $ Var2 Var3 Var4 Var5 : $20.;
cards;
Gender . . . East
Male 25 28 30 East
Female 12 45 14 East
Gender . . . West
Male 10 5 4 West
Female 4 14 3 West
Gender . . . Osceola
Female 1 74 21 Osceola
Male 41 25 32 Osceola
;
run;
proc sql;
select distinct var5 into : list separated by '-' from have;
create table want as
select * from have
union
select var1,sum(var2) as var2,sum(var3) as var3,sum(var4) as var4,"&list" as var5 length=20
from have
group by var1
order by var5;
quit;
This is the point where I ask: What will you do with the resulting data set? You now have records that are not obvious as being composites of others. The apparent task is much more of a REPORT.
And also provide details of "how it doesn't work". We don't know if 1) there was no output, 2) the output was not as needed for the provided data, 3) there was a syntax error and the code didn't run or 4) something related to incomplete description of task.
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!
What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.
Find more tutorials on the SAS Users YouTube channel.