How to union , when same column has different formats

pdhokriya — Thu, 03 Dec 2020 04:59:18 GMT

Hi Reader,

How to union/set , when same column has different formats.

1st dataset has - Sponsor records which is converted to Discontinued.

2nd dataset has - Sponsor which I required as it is.

proc format;
value $ fmt
'COMPLETED' = 'Completed'

"SPONSOR" = 'Discontinued'

;

run;

So in final output - required.

Discontinued

SPONSOR

- Thanks

Re: How to union , when same column has different formats

andreas_lds — Thu, 03 Dec 2020 05:56:40 GMT

Sorry, but i don't understand the problem you have, maybe you could add the data you have in usable form (or at least as table) so that is more obvious what you are taking about.

Re: How to union , when same column has different formats

RichardDeVen — Thu, 03 Dec 2020 11:27:45 GMT

You want to stack data so the formatted value is different based on which table the value came from. However, Proc SQL will assign a column the format that is from the first table in the UNION and a column can have only one format.

Proc FORMAT maps a raw value to a single formatted value. (And, yes, a multi-label format (MLF) can map a single value to more than one value and some Procedures are MLF 'aware')

In your case MLF is not plausible, leaving you with a conundrum.

You will need some data transformation to get the desired result.

Suppose the column in the union contains the formatted values and the original raw values are kept unformatted in a separate new column.

SQL Example

proc format;
  value $ fmt_one
    'COMPLETED' = 'Completed'
    'SPONSOR' = 'Discontinued'
  ;

  value $ fmt_two
    'COMPLETED' = 'Completed'
  ;

run;

data Table_one;
  id = 1; task = 'COMPLETED'; output;
  id = 2; task = 'SPONSOR';   output;

  format task $fmt_one.;
run;

data Table_two;
  id = 3; task = 'COMPLETED'; output;
  id = 4; task = 'SPONSOR';   output;

  format task $fmt_two.;
run;

proc sql;
  * task gets format from table_one;
  create table table_union12 as 
  select *, 'table_one' as source from table_one UNION
  select *, 'table_two' as source from table_two
  ;

  * task gets formatted value per format and data set;
  create table table_union_wanted (drop=_task)
  as
    select *
      , put(_task,$fmt_one.) as task 
      , _task as raw_task
      , 'table_one' as source
    from table_one (rename=task=_task)
  UNION
    select *
      , put(_task,$fmt_two.) as task 
      , _task as raw_task
      , 'table_two' as source
    from table_two (rename=task=_task)
  ;

ods html file='problem.html';


proc print data=table_one; title "Table_One with task using FMT_ONE";
proc print data=table_two; title "Table_Two with task using FMT_TWO";
proc print data=table_union12; title "Tables unioned, task is FMT_ONE";
proc print data=table_union_wanted; title "Tables unioned, task is not formatted";
run;

ods html close;

Output

Re: How to union , when same column has different formats

pdhokriya — Thu, 03 Dec 2020 11:53:31 GMT

@RichardADeVenezia : Thank you so much for the quick reply. and help.

topic Re: How to union , when same column has different formats in SAS Programming

How to union , when same column has different formats

Re: How to union , when same column has different formats

Re: How to union , when same column has different formats

Re: How to union , when same column has different formats