BookmarkSubscribeRSS Feed
FLINT
Calcite | Level 5
Can anyone help me with the SAS equivalent of following code
MERGE TABLE A USING
(SELECT VAR1,VALUE1 FROM TABLE B) C
ON C.VAR1=A.VAR1 WHEN MATCHED THEN UPDATE
SET A.VALUE1= C.VALUE1
4 REPLIES 4
RW9
Diamond | Level 26 RW9
Diamond | Level 26

Something like (nothing to check with):

data want (drop=val1);
  merge tablea (in=a) tableb (in=b keep=var1,value1 rename=(value1=val1));
  by var1;
  if a and b then value1=val1;
run;

Do be aware that both tablea and b both need to be sorted by var1 beforehand.

FLINT
Calcite | Level 5
Here the issue is that I cannot sort one of the tables as its a production table
Kurt_Bremser
Super User

Then sort it to an intermediary in WORK and use that for the merge.


@FLINT wrote:
Here the issue is that I cannot sort one of the tables as its a production table

 

RW9
Diamond | Level 26 RW9
Diamond | Level 26

Datastep merge requires that both datasets are sorted by the by variables.  This is a hard and fixed rule.  There is nothing stopping you making a copy of the datasets, sorting and merging those.  

Other alternatives include using a proc sql code block and rewrite the code you have to be base ANSI, maybe something like:

proc sql;
  create table want as 
  select  a.var1,
          case when b.value1 ne "" then b.value1 else a.value1 end as value1
  from    tablea a
  left join tableb b
  on       a.var1=b.var1;
quit;

This still of course does the sorts, its just implicit in the SQL parser to do this rather than you writing the code.

Another way could be hash table, not yet had a reason to use these, so look up some of the plenty of examples on the web or here.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 4 replies
  • 799 views
  • 0 likes
  • 3 in conversation