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!
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.
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.
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;
@Ksharp and @PaigeMiller: Thank you! Both are practial solutions!
It's finally time to hack! Remember to visit the SAS Hacker's Hub regularly for news and updates.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Ready to level-up your skills? Choose your own adventure.