Preserve Format value in dataset

[dennis_oz]

Hi,

I need some help. I have a column which has a format applied on the column. So before double clicking on the first row there was another value displayed. On clicking it displays '01'. 

The issue is when when I try to append this dataset to another dataset the format value is lost and '01'  is retained.

How can I retain/preserve this format value rater than have the underlying value.

I hope I am clear on question guys. any help will be appreciated.

thanks.

[PeterClemmensen]

Do you use Proc Append?

[ballardw]

---

@dennis_oz wrote:

Hi,

I need some help. I have a column which has a format applied on the column. So before double clicking on the first row there was another value displayed. On clicking it displays '01'. 

The issue is when when I try to append this dataset to another dataset the format value is lost and '01'  is retained.

How can I retain/preserve this format value rater than have the underlying value.

I hope I am clear on question guys. any help will be appreciated.

thanks.

---

Means that the format is not applied to the base data set . Use Proc Datasets to modify the data set in place to apply the format to the variable.

[Reeza]

Looks like one data set has the format applied and one does not. I'm also guessing both are character which makes this slightly more difficult as you need to convert the variable before you append. 

data data2append;
set data2append;
activity_type = put(activity_type, $activity_fmt.);
run;

proc append base=have data=data2append;
run;

If you're doing it via a data step you may be able to do it inline (untested);

data want;
set have1(in=t1) have2 (in=t2);
if t2 then activity_type = put(activity_type, $activity_fmt.);
run;

---

@dennis_oz wrote:

Hi,

I need some help. I have a column which has a format applied on the column. So before double clicking on the first row there was another value displayed. On clicking it displays '01'. 

The issue is when when I try to append this dataset to another dataset the format value is lost and '01'  is retained.

How can I retain/preserve this format value rater than have the underlying value.

I hope I am clear on question guys. any help will be appreciated.

thanks.

---

[Patrick]

I believe there are two separate cases here.

1. Using SAS EG (and I believe same for SAS Studio) and you double click into a cell in the data grid you get into edit mode. The cell that's active then shows you the internal value and not the formatted value. If you would want to edit data this way you would need to change the internal value so it makes sense for SAS to show the value this way.

2. When appending a table to an existing table via Proc Append then the table attributes of the base table get used. If the variable in the base table hasn't the format applied then the new appended data also won't show the variable formatted. SAS formats are on column (variable) level and not on cell level applied.

If you use a SAS data step like below then table master gets re-created. SAS creates the variables in the PDV based on the first occurrence it finds. For below code it would find the variable first in source table master and though use this definition (which in your case is likely the variable without the format applied).

data master;
   set master new;
run;

Below code sample demonstrates how you can apply a permanent format to a pre-existing SAS table without having to fully re-process all the data. Formats are "metadata". Proc Datasets is the SAS procedure for manipulation of SAS table metadata.

proc format;
  value $activity_type
  '1'= 'A 1'
  '2'= 'A 2'
  other= 'A x'
  ;
run;

data work.new;
  do var='1','2','3';
    output;
  end;
  format var $activity_type.;
run ;

data work.master;
  var='5';
  output;
run;

proc append base=work.master data=work.new;
run;

/* apply permanent format to variable var in ds master */
proc datasets lib=work nolist;
  modify master;
    format var $activity_type.;
  run;
quit;

proc print data=master;
run;
 

 

[Tom]

Run PROC CONTENTS on both datasets.  Assuming the two datasets are named NEW and OLD so that you used code like this to add the new data to the old dataset.

proc append base=old data=new;
run;

Then the PROC CONTENTS code would look like:

proc contents data=old; run;
proc contents data=new; run;

What does it show as the TYPE and LENGTH of the variable in each dataset?  

What does it show as the FORMAT (if any) that is attached to the variable in each dataset?

The TYPE and LENGTH is what DEFINES how the value is STORED.

The FORMAT is just special instructions you have told SAS to use when DISPLAYING the values stored in the variable.

You might also want to see what types of values are in each dataset.  For example you could use PROC FORMAT to see the range of values. Remember to REMOVE any format that might be attached to the variable so you can see that ACTUAL values that are in the dataset.

proc freq data=old;
  tables activity_type ;
  format activity_type;
run;
proc freq data=new;
  tables activity_type ;
  format activity_type;
run;

IF the TYPE and LENGTH are the same (or at least the length in OLD is long enough for the longest actual value in NEW) AND the types of values are the same then perhaps you just need to apply the format to the OLD dataset.

proc dataset nolist lib=WORK;
modify OLD;
  format activity_code $fmtname.;
run;
quit;

But if it looks like the OLD dataset has the the DECODED values stored instead of the ACTUAL values then you are right you do need to modify the data before appending it.  

So make a copy of NEW (don't destroy your source data) and make the correction. Then append that modfied copy to the OLD dataset.

Here is a method.  Copy the data from NEW, but rename the variable that needs fixing.  You can pull the the definition of the target variable from the OLD dataset (but don't pull in any of the actual data).  Then you can use the VVALUE() function to get the formatted value of the renamed variable and put it into the right variable.

data copy ;
  set new (rename=(activity_code = _activity_code)) old(obs=0 keep=activity_code));
  activity_code =Vvalue(_activity_code);
  drop _activity_code;
run;
proc append base=OLD data=COPY;
run;