- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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!
Accepted Solutions
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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.
Paige Miller
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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.
Paige Miller
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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;
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
@Ksharp and @PaigeMiller: Thank you! Both are practial solutions!