SAS Programming

DATA Step, Macro, Functions and more
BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Konkordanz
Pyrite | Level 9

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!

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
PaigeMiller
Diamond | Level 26

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

View solution in original post

3 REPLIES 3
PaigeMiller
Diamond | Level 26

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
Ksharp
Super User
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;
Konkordanz
Pyrite | Level 9

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

sas-innovate-wordmark-2025-midnight.png

Register Today!

Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.


Register now!

How to Concatenate Values

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 3 replies
  • 1789 views
  • 2 likes
  • 3 in conversation