topic Re: New column with value by group in SAS Programming

New column with value by group

Konkordanz — Fri, 06 Aug 2021 12:26:01 GMT

Hi,

Ive a dataset with a column "city" (each city exists multiple times) and a column with "prices" (€, sometimes also multiple times, but always the same price). In the price-coulumn there are many empty cells. What I want is to fill the empty cells with the city specific prices. How is this doable? Maybe with Alter table?

Have:

city	price
Dresden	2005
Berlin
Frankfurt
Bonn
Berlin	4030
Bonn
Dresden	2005
Frankfurt	6053
Berlin
Frankfurt
Dresden
Bonn
Berlin	4030
Frankfurt
Bonn	3050
Dresden
....n	....

want:

city	price	price2
Dresden	2005	2005
Berlin		4030
Frankfurt		6053
Bonn		3050
Berlin	4030	4030
Bonn		3050
Dresden	2005	2005
Frankfurt	6053	6053
Berlin		4030
Frankfurt		6053
Dresden		2005
Bonn		3050
Berlin	4030	4030
Frankfurt		6053
Bonn	3050	3050
Dresden		2005
...n	....	....

Thank you for help!

Re: New column with value by group

PaigeMiller — Fri, 06 Aug 2021 12:34:33 GMT

UNTESTED CODE

proc sql;
    create table want as select a.*,b.price as price2 from
    have as a left join (select city,max(price) as price from have group by city) as b
    on a.city=b.city;
quit;

If you want tested code (and this is always a good idea anyway, @Konkordanz ) provide the data as SAS data step code (instructions) and not as a screen capture, something you should just do automatically, every time you ask a question; and from now on we will expect data in this form and not as screen captures.

Re: New column with value by group

Ksharp — Fri, 06 Aug 2021 13:01:29 GMT

data have;
infile cards truncover;
input city :$20.	price;
cards;
Dresden	2005
Berlin	 
Frankfurt	 
Bonn	 
Berlin	4030
Bonn	 
Dresden	2005
Frankfurt	6053
Berlin	 
Frankfurt	 
Dresden	 
Bonn	 
Berlin	4030
Frankfurt	 
Bonn	3050
Dresden	 
;

data want;
 if _n_=1 then do;
  declare hash h(dataset:'have(where=(price2 is not missing) rename=(price=price2))');
  h.definekey('city');
  h.definedata('price2');
  h.definedone();
 end;
set have;
call missing(price2);
rc=h.find();
drop rc;
run;

Re: New column with value by group

Konkordanz — Mon, 09 Aug 2021 07:50:16 GMT

@Ksharp and @PaigeMiller: Thank you! Both are practial solutions!