06-26-2013 09:02 AM
Hello ! I need help about a table :
I woulf like to have just one row if the modes of the variables [ GRD and categ ] appear more than one time in the table : i would like to have the freqb in the same row as freqa.
So at the end just want to have one line per [grd and categ] without repetitions and with the correct informations for freqa and freqb.
I tested with a left join but it doesn't work.
Somebody has a solution?
Thanks a lot !
06-27-2013 07:55 AM
As a general rule its probably easier to get help if you post a sample of the data and not a screen shot.
Try this to see if it gets you anywhere, (FYI - you can combine the queries to make a large single query but I kept it separated to help show what is going on) -- This is untested :
create table cat as
select distinct grd, categ
create table fA as
select distinct freqa, grd, categ
create table fb as
select distinct freqb, grd, categ
create table final as
select a.grd, a.categ, b.freqa, c.freqb
from cat as a left join fa as b on a.grd=b.grd and a.categ=b.categ
left join fb as c on a.grd=c.grd and a.categ=c.categ
06-27-2013 09:00 AM
Thanks esjackso1 your answer was an inspiration ! And it's ok now I work with confidential data so i can't give it here. I could create a factice file but i didn't think about that, next time
Thank you and have a nice day !
create table partG1 as
select distinct GRD, categ, site
create table partH1 as
select distinct freqa, GRD, site,categ
create table partI1 as
select distinct freqb, GRD, site,categ
create table partJ1 as
select a.GRD, a.site, a.categ, b.freqa, c.freqb
from partG1 as a
left join partH1 as b on a.GRD=b.GRDand a.site=b.site and a.categ=b.categ
left join partI1 as c on a.GRD=c.GRDand a.site=c.site and a.categ=c.categ
where freqa ne 0 OR freqb ne 0
create table partk1 as
select *, count(categ) as count
group by GRD, categ;
data partl1 (drop=count);
if count=3 then do;
if freqa = 0 OR freqb = 0 then delete;