topic Re: making the same format in every dataset in SAS Programming

making the same format in every dataset

Jedrzej — Thu, 24 Aug 2017 11:30:55 GMT

hi,

i have two data sets

set a

set b

they have the same variables but different formats values. Is there an easy way to make for example formats in set b the same as in set a ?

Re: making the same format in every dataset

LinusH — Thu, 24 Aug 2017 11:35:15 GMT

Not sure exactly what you mean. Do you want to merge the format domain values, or use formats from b instead of in a?

Give a simple example to illustrate your problem.

Re: making the same format in every dataset

Kurt_Bremser — Thu, 24 Aug 2017 11:51:35 GMT

You can automate such a process by using dictionary.columns and a data step with call execute:

data work.class;
set sashelp.class;
run;

%let library=work;
%let col=age;
%let form=z10.;

proc sql;
create table control as
select memname from dictionary.columns
where upcase(libname) = upcase("&library.") and upcase(name) = upcase("&col.");
quit;

data _null_;
set control end=done;
if _n_ = 1
then call execute("proc datasets library=&library. nolist;");
call execute('modify ' !! trim(memname) !! ';');
call execute("attrib &col. format=&form.;");
if done then call execute('run;quit;');
run;

You will see that dataset WORK.CLASS now displays the age with leading zeroes.

Re: making the same format in every dataset

Jedrzej — Thu, 24 Aug 2017 11:51:40 GMT

example

format of variable abc in set a is decimal

variable abc in set in set b is integer.

now i want variable abc in set a to be alson an integer.

Re: making the same format in every dataset

Oligolas — Thu, 24 Aug 2017 11:52:01 GMT

Hi,

if your columns are same in both datasets try this:

/*create test data*/
proc format;
   value $ gender
   'M'='Male'
   'F'='Female'
   ;
run;

data test1;
   set sashelp.class;
   format sex $gender.;
run;

data test2;
   set sashelp.class;
run;

data vcolumn;
   set sashelp.vcolumn;
   where libname eq 'WORK' and memname eq 'TEST1';
run;

/*test code*/
data _NULL_;
   set vcolumn end=last;
   if _N_ eq 1 then call execute('data test2; set test2;');
   if not missing(format) then call execute('format '||strip(name)||' '||strip(format)||';');
   if last then call execute('run;');
run;

Re: making the same format in every dataset

RW9 — Thu, 24 Aug 2017 12:06:30 GMT

Whilst you have some examples of how to do it technically, I would question why you need to do this. I mean if you have decimal values in a dataset, is it really a good idea to be displaying anything other than decimal - proc report output and such like would use the formatted values, but code wouldn't so you may end up with discrepantions and confusion. I would fix why exactly this became an issue in the first place. I have a fiver says this is linked to importing Excel files as normal.

Re: making the same format in every dataset

Ksharp — Thu, 24 Aug 2017 12:31:27 GMT

I don't understand. Once you set them together, the table b will follow the a's format .
You don't need to do anything .



data a;
 set sashelp.class;
 keep weight;
 format weight f10.2;
run;
data b;
 set sashelp.class;
 keep weight;
 format weight f10.;
run;

data want;
 set a b;
run;

Re: making the same format in every dataset

Jedrzej — Thu, 24 Aug 2017 12:48:34 GMT

but also there are variables with different types : in set a var is numeric in set b var is char so setting them won't work

Re: making the same format in every dataset

Kurt_Bremser — Thu, 24 Aug 2017 12:59:07 GMT

@Jedrzej wrote:
but also there are variables with different types : in set a var is numeric in set b var is char so setting them won't work

Then you need to align your variable types first. You can't have the same format for variables of different type, formats are either for numeric (no prefix) or character (prefix $) variables.

Re: making the same format in every dataset

Kurt_Bremser — Thu, 24 Aug 2017 13:01:29 GMT

PS the fact that you not only have different formats, but also have incompatible types lets me suspect that your ETL process is faulty and probably based on using proc import, which is not suited for production work.