CODEWITHSUNDEEP
sas
user2146441
user2146441

Reputation: 228

Updating data from one table into another

I have two tables TrimmedMeans and Need. Both have an ID column called node which is formatted as follows:

proc format lib=work; value $node_group  (NOTSORTED)
    'LW', 'WL'='LW/WL'
    'LLW', 'LWL', 'LWW'='LLW/LW*'
    'WLL', 'WLW', 'WWL'="WL*/WWL"
    'LLLW', 'LLWL', 'LLWW','LWLL'='LLLW/LLW*/LWLL'
    'LWLW', 'LWWL','LWWW','WLLL','WLLW','WLWL'='LWLW/LWW*/WLL*/WLWL'
    'WLWW','WWLL','WWLW','WWWL'='WLWW/WWL*/WWWL'
;quit;

I do the update as follows: 

proc sort data=trimmedMeans out=trimmedMeans; by node; run;
proc sort data=need out=need; by node; run;
data need;
  update need(in=H1) trimmedMeans(in=H2);
  by node;
  if(H1);
run;

However, it only updates the value of nodes that are called 'L', 'LLLLL', 'WWLW' etc. None of the nodes that contains astericks or forward slashes are updated. I've tried removeing the astericks and forward slashes and replacing them with dashes, but to no avail. Should I be escaping these characters or something? I've checked that the format and length of the 'node' column in both tables is identical.

Upvotes: 1

Views: 156

Answers (2)

Longfish
Longfish

Reputation: 7602

As @Joe pointed out, it may be that you are mixing formats and values. If both datasets are storing the underlying value, formatted using $node_group, then it is possible to update/merge them by the formatted values using the GROUPFORMAT option in the BY statement. This option can only be used in a data step, so is not available for procedures. Here's an example of how it works, hope it helps.

proc format;
value $test 'A'-'I' = 'A-I'
            'J'-'Q' ='J-Q'
            'Q'-'Z' ='Q-Z';
run;

data have1;
input id $ val1;
format id test.;
datalines;
A   1
L   2
R   3
;
run;

data have2;
input id $ val1;
format id test.;
datalines;
D   10
P   20
Y   30
;
run;

data want;
update have1 (in=a) have2 (in=b);
by groupformat id;
run;

Upvotes: 2

Joe
Joe

Reputation: 63424

It sounds like you have one dataset that has things that should evaluate to the formatted result, and one that contains the formatted result. That isn't quite how formats work. In particular, MERGE/UPDATE do not respect formatted values; they only consider underlying values. So if you want the MERGE/UPDATE to merge on the formatted value, you need to PUT the result of the formatting to a new variable.

From the comments, a PROC TABULATE example:

proc format;
value age 
11-13 = '11-13'
14-16 = '14-16';
quit;

proc tabulate data=sashelp.class;
class age;
format age AGE.;
tables age*pctn;
ods output table=mydata;
run;



proc tabulate data=sashelp.class;
class age/mlf;
format age AGE.;
tables age*pctn;
ods output table=mydata;
run;

The first proc tabulate keeps the unformatted value, and uses the first value (I am not sure if it's the first value it encounters or the first value sorted lowest to highest). So 11 and 14 are the raw values. The second proc tabulate with mlf (multilabel format) will force it to be character, using the formatted value.

Upvotes: 1

Related Questions