DATA Step, Macro, Functions and more

making the same format in every dataset

Accepted Solution Solved
Reply
Contributor
Posts: 26
Accepted Solution

making the same format in every dataset

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 ?

 

 


Accepted Solutions
Solution
‎09-07-2017 06:00 AM
Regular Contributor
Posts: 165

Re: making the same format in every dataset

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;
________________________

- Cheers -

View solution in original post


All Replies
Super User
Posts: 5,920

Re: making the same format in every dataset

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.

Data never sleeps
Contributor
Posts: 26

Re: making the same format in every dataset

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.

Super User
Super User
Posts: 9,862

Re: making the same format in every dataset

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.

Super User
Posts: 10,600

Re: making the same format in every dataset

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Solution
‎09-07-2017 06:00 AM
Regular Contributor
Posts: 165

Re: making the same format in every dataset

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;
________________________

- Cheers -

Super User
Posts: 10,850

Re: making the same format in every dataset

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;

Contributor
Posts: 26

Re: making the same format in every dataset

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
Super User
Posts: 10,600

Re: making the same format in every dataset

[ Edited ]

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Super User
Posts: 10,600

Re: making the same format in every dataset

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 9 replies
  • 225 views
  • 2 likes
  • 6 in conversation