Merge by variables

Reply
Occasional Contributor
Posts: 9

Merge by variables

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.

table.png

Somebody has a solution? Smiley Happy

Thanks a lot !

Super Contributor
Posts: 578

Re: Merge by variables

what does your dataset look like?

Occasional Contributor
Posts: 9

Re: Merge by variables

My dataset appear like a picture in my topic, can't you see it?

Super Contributor
Posts: 333

Re: Merge by variables

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  :

proc sql;

     create table cat as

     select distinct grd, categ

     from yourdata

     ;

     create table fA as

     select distinct freqa, grd, categ

     from yourdata

     '

     create table fb as

     select distinct freqb, grd, categ

     from yourdata

     ;

     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

     ;

quit;

Occasional Contributor
Posts: 9

Re: Merge by variables

Thanks esjackso1 your answer was an inspiration ! And it's ok now Smiley Happy 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 Smiley Happy

Thank you and have a nice day !

Proc sql;

  create table partG1 as

    select distinct GRD, categ, site

    from mydata;

  create table partH1 as

    select distinct freqa, GRD, site,categ

    from mydata;

  create table partI1 as

    select distinct freqb, GRD, site,categ

    from mydata;

  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

    from partj1

    group by GRD, categ;

quit;


data partl1 (drop=count);

  set partk1;

  if count=3 then do;

    if freqa = 0 OR freqb = 0 then delete;

  end;

run;

Ask a Question
Discussion stats
  • 4 replies
  • 261 views
  • 3 likes
  • 3 in conversation