topic Two different formats under one variable in SAS Programming

Two different formats under one variable

luvscandy27 — Fri, 12 Jan 2024 11:56:18 GMT

I am aware that formats follow variables however, I would like to know if there is a way

to have two formats under one variable? For example, the code below pulls data in and

all variables are character variables. The data is split into two datasets and formatted.

The two datasets are then stacked to create one variable called estimate. The estimate

variable should hold numeric values which are comma formatted and decimal values. However,

the decimals are dropped because the variable is now based on the comma value.

data have;
input year $ number $ percent $;
cards;
2018 515 29.1
2018 4526 33.3
2018 26518936 56.1
;
run;

/*Create two separate datasets: 1)numbers with comma format 2)percent with decimal format*/
data number (drop=percent number);
set have;
estimate = input(number,best.);
format estimate comma10.0;
run;

data percent (drop=number and percent);
set have;
estimate = input(percent,best.);
run;

/*Stack datasets*/
data wrong;
set number percent;
run;

This is what I get using the above code:

Year	Estimate
2018	515
2018	4,526
2018	26,518,936
2018	29
2018	33
2018	56

This is what I would like to have:

Year	Estimate
2018	515
2018	4,526
2018	26,518,936
2018	29.1
2018	33.3
2018	56.1

Does a way exist to make this dataset happen?

Re: Two different formats under one variable

PaigeMiller — Fri, 12 Jan 2024 12:03:29 GMT

If it has to be a SAS data set with two different formats for a single variable, then this is not possible. Nor can I think of a reason why you would want to do this, as the purpose of a SAS data set is to contain data. The data is the same, regardless of how it is formatted.

However, if you are creating a report from a SAS data set, then yes, it does make sense to do this, and you can have two (or more!) different formats in a column by using PROC REPORT. See PROC REPORT — different formats for different rows? - SAS Support Communities

Re: Two different formats under one variable

Patrick — Fri, 12 Jan 2024 12:25:50 GMT

Don't forget that formats only affect how values get displayed and not how they get stored internally. You can always change the format applied to variable - permanently or temporarily. With your code try below and see what happens.

/*Stack datasets*/
data wrong;
  set number percent;
  format estimate best32.4;
run;

proc print data=wrong;
run;

Re: Two different formats under one variable

Astounding — Fri, 12 Jan 2024 13:28:27 GMT

First, note that you can do this. You would have to define (and apply) your own format along these lines:

proc format;
   value some_commas 100-high = [comma10.0]
    other=[5.1];
run;

Then in your FORMAT statement:

format myvar some_commas.;

You probably will want to examine the results and possibly apply centering as the default for the format. But try this much first and see how far it takes you.

You will need to apply a rule, as is done here. For example, everything that is less than 100 prints with a decimal point. It won't matter what data set the value came from.

Re: Two different formats under one variable

Patrick — Sun, 14 Jan 2024 02:00:58 GMT

There are already solutions proposed that will return what you've asked for but... Looking at your data again does it really make sense to mix "absolute" numbers and percentages in the same column? How would you separate them for any calculations? If this is just about some report then consider something like below:

data have;
  input year $ number $ percent $;
  datalines;
2018 515 29.1
2018 4526 33.3
2018 26518936 56.1
;

proc sql;
  select year, 'number' as type, number as estimate
  from have
  union all
  select year, 'percent' as type, percent as estimate
  from have
  ;
quit;