topic retain various formats in common variables after combining datasets in New SAS User

retain various formats in common variables after combining datasets

JCO1 — Mon, 26 Aug 2019 18:55:21 GMT

The yr variable with a range of 2005-2016 becomes separate variables when the data are transformed. The variable stat has three values, "Deaths," "CMR", and "AAMR" which I format with 0, 1, and 2 decimal places, respectively. They appear that way in the datasets death2, cmr2, and aamr2 columns '2005'N-'2016'N . When I combine the datasets, the '2005'N-'2016'N columns are all formatted to 1 decimal place, instead of 0, 1, and 2 places that I want. The original data have 0 decimals for dths, 8 decimals for CMR and 8 decimals for AAMR. If I comment out the format lines below, the combined dataset has 0, 8, and 8 decimals in the '2005'N-'2016'N columns, demonstrating that they do not have to take the same format. Adding to my perplexity is that my program seemed to work yesterday as intended. I have tried closing and reopening SAS 9.4

proc sort data=AAMRtabout; by dthrank CODnum2 sex2; run;

proc transpose data=AAMRtabout out=dths_t (drop=_NAME_);

by dthrank CODnum2 sex2;

id yr;

var dths_sum;

run;

data death2;

set dths_t;

stat="Deaths";

run;

proc transpose data=AAMRtabout out=cmr_t (drop=_NAME_);

by dthrank CODnum2 sex2;

id yr;

var CMR_sum;

run;

data cmr2;

set cmr_t;

stat="CMR";

format '2005'N-'2016'N comma8.1;

run;

proc transpose data=AAMRtabout out=aamr_t (drop=_NAME_);

by dthrank CODnum2 sex2;

id yr;

var AAMR_sum;

run;

data AAMR2;

set aamr_t;

stat="AAMR";

format '2005'N-'2016'N comma8.2;

run;

data AAMRtabout_final;

retain dthrank CODnum2 sex2 stat;

set death2 cmr2 aamr2;

run;

Re: retain various formats in common variables after combining datasets

PGStats — Mon, 26 Aug 2019 19:51:47 GMT

You cannot have multiple formats for the same variable in SAS. The format is a property of the variable, not of every value, as it is in Excel.

Variable names such as '2005'n are tolerated in SAS, but they will not be created by SAS procedures. Proc transpose will create variable names from numeric fields by prefixing the formatted ID numbers with "_". So, the new variable names will be _2005-_2016.

Re: retain various formats in common variables after combining datasets

Tom — Mon, 26 Aug 2019 19:52:05 GMT

I cannot match your comments to the code you posted.

First thing to remember that each variable can have only one format attached to it that will apply to all observations in the dataset. Just like it has only one name, type, length or label.

If you rotate up many observations into separate variables, as you are doing with your ID statements, then all of the variables created will inherit any format that might have been attached to the variable listed in the VAR statement.

If you combine many datasets as you are doing in your last step then the format attached to the variable in the output dataset will be the first format found for that variable among input datasets. Note that datasets that either do not contain the specific variable (so perhaps some of your data did not contain data for one of the years) or if they have the variable but no format has been permanently attached then it will be ignored when trying to determine the format to attach. So if DEATH2 has '2014'n defined with no format attached and CMR has '2014'n defined with COMMA8.1 then the variable will use COMMA8.1. Unless of course your data step attaches a format to the variable. In that case the format defined by the FORMAT statement will override any format attached in the input datasets.

Re: retain various formats in common variables after combining datasets

JCO1 — Tue, 27 Aug 2019 13:15:35 GMT

Thanks. Both replies were not wrong. I converted the year variables from numeric to character and assigned them the formats desired, e.g.
data death2;
set dths_t;
stat="Deaths";
_2005=put('2005'N, comma8.0);
data cmr2;
set cmr_t;
stat="CMR";
_2005=put('2005'N, comma8.1);